Cross Table with multiple dimension is not working

[srini]

Hi Friends, I am trying this code to cross table with two dimension 

CrossTable(Month,Value,2)
Data:
Load
Country,
Category,
"M01 Units"&'|'&"M01 Charge (000s USD)",
"M02 Units"&'|'&"M02 Charge (000s USD)",
"M03 Units"&'|'&"M03 Charge (000s USD)",
"M04 Units"&'|'&"M04 Charge (000s USD)",
"M05 Units"&'|'&"M05 Charge (000s USD)",
"M06 Units"&'|'&"M06 Charge (000s USD)",
"M07 Units"&'|'&"M07 Charge (000s USD)",
"M08 Units"&'|'&"M08 Charge (000s USD)",
"M09 Units"&'|'&"M09 Charge (000s USD)",
"M10 Units"&'|'&"M10 Charge (000s USD)",
"M11 Units"&'|'&"M11 Charge (000s USD)",
"M12 Units"&'|'&"M12 Charge (000s USD)"
FROM [lib://model/Model file.xlsx]
(ooxml, embedded labels, header is 3 lines, table is source);

// Resident Data;

// Drop Table Data;

Test1:
Load
Country,
Category,
Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM')) as [Month],
Num(Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM'))) as [MonthNum],
Units
Resident Data;
// Drop Table Test;

Final:
LOAD
Country,
Category,
  KeepChar(SubField([Month], '|', 1), 'M010203040506070809101112') as Month,
  SubField(Value, '|', 1) as Units,
  SubField(Value, '|', 2) as Charges
Resident Data;

DROP Table Data;

which is not giving me the result and giving me the error of not finding Month field but as per the code month field is there in the cross table.

[held_florian]

Final:
LOAD
Country,
Category,
  KeepChar(SubField([Month], '|', 1), 'M010203040506070809101112') as Month,
  SubField(Value, '|', 1) as Units,
  SubField(Value, '|', 2) as Charges
Resident Data; 

is the resident right?

I think the following reference is correct, right?

Resident Test1;

[sunny_talwar]

Looks okay...

[dplr-rn]

1 suggestion. remove the code after the cross table load and check result.

Difficult to diagnose with out some sample data. high level it looks ok
Whats the need for 2 resident loads on Data table?

[srini]

Hi Friends,

Thanks for your reply!

I have executed the script table by table...it is not showing any error but in the visualization it is not giving any  values. Instead of values it is showing only Zeros, and when I populate it is not showing any month in the dimension. I know it is difficult to analyze the code without sample data and app, hence I have uploaded the app and source data.

CrossTable(Month,Value,2)
Data:
Load
Country,
Category,
"M01 Units"&'|'&"M01 Charge (000s USD)",
"M02 Units"&'|'&"M02 Charge (000s USD)",
"M03 Units"&'|'&"M03 Charge (000s USD)",
"M04 Units"&'|'&"M04 Charge (000s USD)",
"M05 Units"&'|'&"M05 Charge (000s USD)",
"M06 Units"&'|'&"M06 Charge (000s USD)",
"M07 Units"&'|'&"M07 Charge (000s USD)",
"M08 Units"&'|'&"M08 Charge (000s USD)",
"M09 Units"&'|'&"M09 Charge (000s USD)",
"M10 Units"&'|'&"M10 Charge (000s USD)",
"M11 Units"&'|'&"M11 Charge (000s USD)",
"M12 Units"&'|'&"M12 Charge (000s USD)"
FROM [lib://model/Model file.xlsx]
(ooxml, embedded labels, header is 3 lines, table is source);

// Resident Data;

// Drop Table Data;

Test1:
Load
Country,
Category,
Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM')) as [Month],
Num(Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM'))) as [MonthNum],
Value
Resident Data;
Drop Table Data;

Final:
LOAD
Country,
Category,
  MonthNum,
KeepChar(SubField([Month], '|', 1), 'M010203040506070809101112') as Month,
  SubField(Value, '|', 1) as Units,
  SubField(Value, '|', 2) as Charges
Resident Test1;
Drop Table Test1;
// DROP Table Data;

latest:
Load
Country,
Category,
If( Category ='ACTUAL' AND MonthNum<=Num(Month(Today())), Units) AS "YTD Actuals",
If( Category ='FORECAST' AND MonthNum<=Num(Month(Today())), Units) AS "YTD FORECAST"
// If( Category ='ACTUAL' AND MonthNum<=Num(Month(Today())), Units) AS "YTD Charges Actuals",
// If( Category ='FORECAST' AND MonthNum<=Num(Month(Today())), Units) AS "YTD Charges FORECAST"
// if(Category="ACTUAL",Sum({$<MonthNum = {"<=$(=Num(Month(Today())))"}>} Units),0) as YTD,
Resident Final;
Drop Table Final;

[srini]

Any updates? Friends!

I keep trying but the same issues 😞

[sunny_talwar]

You are dropping the final table here? is there a reason you are dropping the only table which contained Units and Charges?

In Qlik View or Qlik Sense, if a table (field) don't exist after the reload has finished, you cannot use them in your application.

[sunny_talwar]

This is what i get if i comment DROP Table Final;

[srini]

 Hi Sunny, 

yes, I aware that... but if I don't drop the table there synthetic keys(table) is getting generated.

Can we use any other options like 'qualify' to overcome this issue? 

and one more thing if I add Month in the dimension in the app it is only showing 'M' not the month name. Earlier it was working fine with one Dimension in the cross table. but it is not working now.

Thanks!