crosstable with for each loop

[vBonini]

Hi, I have a table like this:

LOAD

   tableDataId&business_name&year&month as key
    tableDataId,
    business_name,
    year,
    month,
    column_name,
    column_field
Resident Dati;

that looks like this:

the blue columns are mandatory, the red columns are the fileds that the user will compile

I would like an output like this: 

I really struggling to obtain this, because the "column_name"   have a lot of different values and I think I should use a "for each" loop

[ramchalla]

Hi,

Please try the below code. attached the sample file and qlikview file.

Data:

LOAD tableDataId, 

     business_name, 

     year, 

     month, 

     column_name, 

     column_field

FROM

[D:\Qlik Sense\crosstable.xlsx]

(ooxml, embedded labels, table is Sheet1);

GenericLoad:

Generic LOAD 

tableDataId, 

      business_name, 

      year, 

      month, 

      column_name, 

      column_field

Resident Data;

FinalTable:

NoConcatenate

LOAD Distinct 

tableDataId,

business_name, 

      year, 

      month 

Resident Data;

for i=0 to NoOfTables()

  TableList:

  LOAD TableName($(i))  as  TableName

  AutoGenerate 1

  Where WildMatch(TableName($(i)), 'GenericLoad*');

NEXT i

for j=1 to FieldValueCount('TableName')

 LET vTable = FieldValue('TableName', $(j));

 Left Join (FinalTable) LOAD * Resident [$(vTable)];

 DROP Table [$(vTable)];

 NEXT j

 DROP Table Data, TableList;

[vBonini]

It gives me Unexpected token: ':', expected one of: 'Where', 'While', 'Group', 'Order'

I think is because the values in column_name contains whitespace (for example there are values like "distinct count of sources of energy")

[ramchalla]

Hi,

try the below one with the same sample data attached.

Data:

LOAD tableDataId, 

     business_name, 

     year, 

     month, 

     column_name, 

     column_field

FROM

[D:\Qlik Sense\crosstable.xlsx]

(ooxml, embedded labels, table is Sheet1);

GenericLoad:

Generic LOAD 

tableDataId, 

      business_name, 

      year, 

      month, 

      column_name, 

      column_field

Resident Data;

Final:

NoConcatenate

LOAD Distinct 

tableDataId,

business_name, 

      year, 

      month 

Resident Data;

Left Join (Final)

LOAD * Resident GenericLoad.kwh;

Left Join (Final)

LOAD * Resident GenericLoad.country;

Left Join (Final)

LOAD * Resident GenericLoad.flag_important;

Left Join (Final)

LOAD * Resident GenericLoad.phone;

DROP Tables Data, GenericLoad.kwh, GenericLoad.country, GenericLoad.flag_important, GenericLoad.phone;

Thanks,

Ramesh.

[vBonini]

HI Ramchall, this is not the solution for me because the column names can be over 50 different values so I must use a for loop statement

[ramchalla]

Hi,

in that case, you can use the code that I pasted earlier. You can find the attached qvw file corresponding to this.

[vBonini]

thank you, but it It gives me Unexpected token: ':', expected one of: 'Where', 'While', 'Group', 'Order'

I think is because the values in column_name contains whitespace (for example there are values like "distinct count of sources of energy")

[marcus_sommer]

I suggest to rethink the entire approach because creating such huge crosstable-structures have often a lot of serious disadvantages. All these new fields must be addressed in the script as well as within the UI, are difficult to categorize, causing extra efforts for all kind of NULL or exception handling and the UI selections become easily a nightmare. Therefore I suggest to keep by the origin data-structure which usually enabled all kind of views.