topic Help with using crosstables in resident load in QlikView

Help with using crosstables in resident load

pgkrsk — Thu, 12 Aug 2021 10:09:11 GMT

Hi,

I have 3 fields: Item, Type and Value. Each item has 2 types, A and B. I would like to have 3 fields: Item, A and B so A would be Value when type is A and B would be Value when type is B. In other words I would like to get rid of Type field so I could sum Type A and B values separately easily. How would I do this, with crosstable?

Thanks in advance.

Re: Help with using crosstables in resident load

chrismarlow — Thu, 12 Aug 2021 10:18:23 GMT

Hi,

I think crosstable would be going the other way, so taking 2 fields A & B & turning them into a type fields, this sounds more like a pivot, so something like;

Load Dim, Sum(If(Type='A',Value)) AS A, Sum(If(Type='B',Value)) AS B Group by Dim; LOAD * INLINE[ Dim, Type, Value X, A, 10 X, B, 5 Y, A, 20 Y, B, 15 ];

Cheers,

Chris.

Re: Help with using crosstables in resident load

Taoufiq_Zarra — Thu, 12 Aug 2021 10:25:08 GMT

@pgkrsk by using Generic Load, like :

Input: LOAD * INLINE [ Item, Type, Value 1, A, 10 2, B, 11 3, A, 12 4, B, 13 ]; DATA: Generic LOAD Item, Type, Value Resident Input; CombinedGenericTable: Load distinct Item Resident Input; Drop Table Input; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); //trace $(i) - '$(vTable)'; IF WildMatch('$(vTable)', 'DATA.*') THEN LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i

output: