Data into Excel

Question

Hi 
 Do we have any plugins for below requirement, 
 Tyre Data Table: 
 Columns: TyreID (Primary Key), PartNumber (Foreign Key) 
 Country Restriction Table: 
 Columns: ID (Primary Key), PartNumber (Foreign Key), Country Name, IsActive (Boolean) These tables have a one-to-many relationship. For example, if we have a tyre with TyreID 1, there could be 150 entries in the Country Restriction Table for this tyre. Let's say for TyreID 1, it is active in India and the US. In this case, the IsActive column would be 1 for India and the US, and 0 for all other countries. 
 Requirement: 
 We need to export the data in the following format: 
 Part Number|Country1|country2|.......country150 
 12345 | 1 | 0 |................|1|

Mike Schmitt · Answer

You might be able to make a View that creates the flattened, row-by-row construct you describe here. It would be very challenging to do it directly in any of the Excel Export options available. If you're able to get such a View working, though, you could export it to excel pretty easily using the Export DSE to Excel node, as far as I know.

Chris · Answer

I'm going to assume the export format is a pipe-delimited CSV file..? 
 In this case, I would create a view to join the 2 tables and you can create an expression to populate a Text Doc from Template service such as below. This shows each line as an array - for the actual file output we would add line breaks with char(13) and join the output into one string for the template. 
 a!localVariables(
 local!data: {
 a!map(TyreID: 1, PartNumber: "ABC", CountryName: "India", IsActive: 1),
 a!map(TyreID: 1, PartNumber: "ABC", CountryName: "US", IsActive: 1),
 a!map(TyreID: 1, PartNumber: "ABC", CountryName: "Sweden", IsActive: 0),
 a!map(TyreID: 1, PartNumber: "ABC", CountryName: "France", IsActive: 0),
 a!map(TyreID: 2, PartNumber: "DEF", CountryName: "India", IsActive: 0),
 a!map(TyreID: 2, PartNumber: "DEF", CountryName: "US", IsActive: 0),
 a!map(TyreID: 2, PartNumber: "DEF", CountryName: "Sweden", IsActive: 1),
 a!map(TyreID: 2, PartNumber: "DEF", CountryName: "France", IsActive: 1)
 },
 local!delimiter: "|",
 local!countries: union(local!data.CountryName,local!data.CountryName),
 local!PartNumber: union(local!data.PartNumber,local!data.PartNumber),
 local!header: joinarray({"PartNumber",local!countries},local!delimiter),
 
 {
 local!header,
 a!forEach(
 items: local!PartNumber,
 expression: {
 concat(
 fv!item,
 local!delimiter,
 joinarray(index(local!data.IsActive,wherecontains(fv!item,local!data.PartNumber),null),local!delimiter)
 )
 }
 )
 }
)