topic Re: Field Values to several Fields in QlikView

Field Values to several Fields

brunosantos — Tue, 23 Jun 2020 16:38:27 GMT

Hi there.

I've been search the Qlik Community, but I cannot seem to find what I want, and probably it's just a matter of choosing the correct keys words. Anyway here goes.

I have a table with subject, test and result, for example:

subject	test	result
a	test1	ok
a	test2	ok
a	test3	nok
b	test1	nok
b	test2	ok
b	test3	nok

And I want to show for each type of test (values from field test) the corresponding result (ok/nok) according to subject, so something like this:

subject	test1	test2	test3
a	ok	ok	nok
b	nok	ok	nok

The closest I've been is with pivot table, but cannot seem to workout the expression to fit the needs (just don't know).

I believe this should be pretty straight forward, or? Any help?

Thanks,

Bruno Santos

Re: Field Values to several Fields

Taoufiq_Zarra — Tue, 23 Jun 2020 17:15:55 GMT

One Solution with Generic Load

DATA: LOAD * INLINE [ subject, test, result a, test1, ok a, test2, ok a, test3, nok b, test1, nok b, test2, ok b, test3, nok ]; CombinedGenericTable: Load distinct subject resident DATA; Tmp: generic load * resident DATA; drop table DATA; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); //trace $(i) - '$(vTable)'; IF WildMatch('$(vTable)', 'Tmp.*') THEN LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i

output :

Re: Field Values to several Fields

brunosantos — Thu, 25 Jun 2020 09:15:13 GMT

Thank you @Taoufiq_Zarra . That is exactly what I need.

And searching a little about Generic Load I've found this post from @hic with further explanation.

Once again thanks.

Regards,

Bruno Santos