topic Crosstable resident load in QlikView

Crosstable resident load

techvarun — Sat, 16 Nov 2024 04:14:18 GMT

ForeCast:
LOAD [Acc Type], 
     //Source, 
     Activity, 
     Product, 
     [31/10/2018] AS [1],
     [30/11/2018] AS [2],
     [31/12/2018] AS [3],  
     [31/01/2019] AS [4], 
     [28/02/2019] AS [5],
     [31/03/2019] AS [6],
     [30/04/2019] AS [7],
     [31/05/2019] AS [8],
     [30/06/2019] AS [9],
     [31/07/2019] AS [10],
     [31/08/2019] AS [11],
     [30/09/2019] AS [12]
FROM
[Plan.xlsx]
(ooxml, embedded labels, table is [Forecast ]);

YTD:
CrossTable(%CalKey, Amount,5)
LOAD [Acc Type], 
     'Forecast' as Source,
     'Actual Forecast' as Scenario,
     Activity, 
     Product, 
    [1]*$(v1) as '31/10/2018', 
    [1]*$(v1)+[2]*$(v2) as '30/11/2018', 
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3) as '31/12/2018', 
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4) as '31/01/2019', 
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5) as '28/02/2019', 
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6) as '31/03/2019', 
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7) as '30/04/2019', 
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8) as  '31/05/2019', 
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9) as '30/06/2019', 
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10)  as '31/07/2019', 
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10) +[11]*$(v11)  as '31/08/2019', 
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10) +[11]*$(v10) +[12]*$(v12)   as '30/09/2019'
Resident ForeCast where [Acc Type]='AVG';

Drop Table ForeCast;

Hi Experts,

Please let me know what is the issue with the code.

Can we not perform a crosstable on a resident load.

Re: Crosstable resident load

Silambarasan1306 — Fri, 08 Mar 2019 09:54:22 GMT

Hey Varun, You can perform crosstable in the resident load, But you cannot use "NoConcatenate" function in the cross table. Remove NoConcatenate and then load the data.

Re: Crosstable resident load

techvarun — Fri, 08 Mar 2019 11:29:46 GMT

Hi Silambarasan,

Not working still.

Re: Crosstable resident load

Silambarasan1306 — Fri, 08 Mar 2019 11:32:17 GMT

Varun, could you please share the sample application or the expected output screenshot

Re: Crosstable resident load

marcus_sommer — Fri, 08 Mar 2019 14:05:09 GMT

Try it with:

ForeCast:
LOAD [Acc Type], 'Forecast' as Source, 'Actual Forecast' as Scenario, Activity, Product,
    [1]*$(v1) as '31/10/2018',
    [1]*$(v1)+[2]*$(v2) as '30/11/2018',
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3) as '31/12/2018',
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4) as '31/01/2019',
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5) as '28/02/2019',
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6) as '31/03/2019',
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7) as '30/04/2019',
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8) as '31/05/2019',
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9) as '30/06/2019',
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10) as '31/07/2019',
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10) +[11]*$(v11) as '31/08/2019',
    [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10) +[11]*$(v10) +[12]*$(v12)   as '30/09/2019';
LOAD [Acc Type], Activity, Product,
     [31/10/2018] AS [1], [30/11/2018] AS [2], [31/12/2018] AS [3], [31/01/2019] AS [4],
     [28/02/2019] AS [5], [31/03/2019] AS [6], [30/04/2019] AS [7], [31/05/2019] AS [8],
     [30/06/2019] AS [9], [31/07/2019] AS [10], [31/08/2019] AS [11], [30/09/2019] AS [12]
FROM [Plan.xlsx] (ooxml, embedded labels, table is [Forecast ]);

YTD:
CrossTable(%CalKey, Amount,5) load * Resident ForeCast where [Acc Type]='AVG';

Drop Table ForeCast;

I think your issue isn't caused from the resident else that a crosstable treated all content as strings and didn't evaluated anything.

- Marcus