Crosstabling multiple rows challenge

[marcel_olmo]

Hi guys,

I have a table like this :

And I'd like to have this desired ouput :

How could I do it?

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

Best regards, Marcel.

[shivakumarthoutam]

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

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

[marcel_olmo]

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 · Accepted solution]

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;

[marcel_olmo]

Thanks @mikaelsc it worked like a charm

Regards, Marcel.