topic Re: Crosstabling multiple rows challenge in App Development

Crosstabling multiple rows challenge

marcel_olmo — Sat, 16 Nov 2024 06:43:02 GMT

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.

Re: Crosstabling multiple rows challenge

shivakumarthoutam — Wed, 30 Jan 2019 18:34:12 GMT

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

Re: Crosstabling multiple rows challenge

marcel_olmo — Wed, 30 Jan 2019 18:47:31 GMT

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.

Re: Crosstabling multiple rows challenge

mikaelsc — Wed, 30 Jan 2019 20:29:11 GMT

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;

Re: Crosstabling multiple rows challenge

marcel_olmo — Thu, 31 Jan 2019 06:34:57 GMT

Thanks @mikaelsc it worked like a charm

Regards, Marcel.