Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 :
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 :
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