Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
Partner
Partner

Crosstabling multiple rows challenge

Hi guys,

I have a table like this :

cross1.png

And I'd like to have this desired ouput :

cross2.png

How could I do it?

I've attached the sample in excel if someone could try it.

Best regards, Marcel.

Labels (1)
1 Solution

Accepted Solutions
mikaelsc
Contributor III

Re: Crosstabling multiple rows challenge

why would someone even give you such a horrible datasource... [tempOriginData]: LOAD [A], [B], [C], [D] FROM [lib://Downloads/SampleCrossData.xlsx] (ooxml, no labels, table is OriginData) ; tempcombinations: crosstable(combination,fieldvalue,1) load [A] as fieldname, [B] as comb1, [C] as comb2, [D] as comb3 resident tempOriginData where wildmatch([A],'field*'); combinations: load Distinct combination Resident tempcombinations; left join(combinations) load combination, fieldvalue as Field1 resident tempcombinations where fieldname = 'Field1'; left join(combinations) load combination, fieldvalue as Field2 resident tempcombinations where fieldname = 'Field2'; left join(combinations) load combination, fieldvalue as Field3 resident tempcombinations where fieldname = 'Field3'; tempValues: crosstable(combination, Value,1) load [A] as date, [B] as comb1, [C] as comb2, [D] as comb3 resident tempOriginData where not wildmatch([A],'field*','*date'); left join (combinations) load * resident tempValues; drop table tempOriginData,tempcombinations,tempValues;
4 Replies
shivakumarthoutam
New Contributor

Re: Crosstabling multiple rows challenge

From what I see, QlikSense automatically loads the data in the desired format. I see and option in OutputDesiredData which has the desired output.

QS1.png

This is my first day with QlikSense, I hope I am making sense

Partner
Partner

Re: Crosstabling multiple rows challenge

Hi @shivakumarthoutam,

this is an excel example I've prepared to show you my need. I only have the first sheet, the second sheet was made by me manually.

The point here is to load sheet1 "Origin Data" and transform it into the "desiredoutputData" via Qlik Sense.

Regards, Marcel.

mikaelsc
Contributor III

Re: Crosstabling multiple rows challenge

why would someone even give you such a horrible datasource... [tempOriginData]: LOAD [A], [B], [C], [D] FROM [lib://Downloads/SampleCrossData.xlsx] (ooxml, no labels, table is OriginData) ; tempcombinations: crosstable(combination,fieldvalue,1) load [A] as fieldname, [B] as comb1, [C] as comb2, [D] as comb3 resident tempOriginData where wildmatch([A],'field*'); combinations: load Distinct combination Resident tempcombinations; left join(combinations) load combination, fieldvalue as Field1 resident tempcombinations where fieldname = 'Field1'; left join(combinations) load combination, fieldvalue as Field2 resident tempcombinations where fieldname = 'Field2'; left join(combinations) load combination, fieldvalue as Field3 resident tempcombinations where fieldname = 'Field3'; tempValues: crosstable(combination, Value,1) load [A] as date, [B] as comb1, [C] as comb2, [D] as comb3 resident tempOriginData where not wildmatch([A],'field*','*date'); left join (combinations) load * resident tempValues; drop table tempOriginData,tempcombinations,tempValues;
Partner
Partner

Re: Crosstabling multiple rows challenge

Thanks @mikaelsc it worked like a charm

Regards, Marcel.