Crosstable resident load

[techvarun]

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.

[Silambarasan1306]

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.

[techvarun]

Hi Silambarasan,

Not working still.

[Silambarasan1306]

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

[marcus_sommer]

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