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.

[sunny_talwar]

I believe you are unnecessarily  complicating things for yourself... try this script

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 (4).xlsx]
(ooxml, embedded labels, header is 3 lines, table is source);
   
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,
    Country&'|'&Category as CountryCategoryKey,
	MonthNum,
	Month,
	SubField(Value, '|', 1) as Units,
	SubField(Value, '|', 2) as Charges
Resident Test1;

DROP Table Test1;

latest:
Load CountryCategoryKey,
	If( Category ='ACTUAL' AND MonthNum<=Num(Month(Today())), Units) AS "YTD Actuals",
	If( Category ='FORECAST' AND MonthNum<=Num(Month(Today())), Units) AS "YTD FORECAST"
Resident Final;

1) synthetic keys can be avoided using composite key Country&'|'&Category as CountryCategoryKey

2) you extracted Month Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM')) as [Month], but then decided to use keepchar on it?

I think all you need to do is to take a step back from this and get the basics right. It isn't too difficult, but just give yourself the time to understand the basics.

I hope this helps.

Best,
Sunny

[srini]

Thanks Friend, 

I have undergone the basics which helps me a lot. But this cross table is always a confused area for me. 

Since we are used cross table now I am not able create a new field which requires Units for month. 

I need a suggestion here can I use the separate table without cross table and can get this?

[sunny_talwar]

I am not entirely sure I understand? Can you explain what exactly are you trying to get?

[srini]

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);

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,
// Country&'|'&Category as CountryCategoryKey,
MonthNum,
Month,
SubField(Value, '|', 1) as Units,
SubField(Value, '|', 2) as Charges
Resident Test1;

DROP Table Test1;

latest:

LOAD Country,
Category,
// CountryCategoryKey,
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())), Charges) AS "YTD Charges Actuals",
If( Category ='FORECAST' AND MonthNum<=Num(Month(Today())), Charges) AS "YTD Charges FORECAST",
IF(Category='Plan',0,IF(Category='Actual',[M01 Units])) as "2018 - 12",

IF(Category='Plan',0,IF(Category='Actual',[M01 Units])) as "2019 - 01",

.

.

.

IF(Category='Plan',0,IF(Category='Actual',[M01 Units])) as "2019 - 12"

For that I have added the last line in the code, but since I have loaded the table as cross table I didn't find M01 Units Field to get the new Field.  So, I need any suggestions to get this new field?

[sunny_talwar]

But you already have Units and Charges for each Month here

Why do you need the values to be in separate fields? Once again, why are you looking to complicate your dashboard? What is the final goal that you are hoping to get to?

[srini]

 Hi Sunny,

Yes, I think I am going crazy on this....

I need to show like this in the table

2018-12         2019-1           2019-2            2019-3..............................2019-12

M01 Units    M02 Units    M03 Units       M04 Units..........................

Thanks Sunny!!

[sunny_talwar]

You can use a pivot table to show the information this way... what is the reason to do this in the script?

[dplr-rn]

Like sunny says use pivot table. that should be the way to go unless theres some reason thats not clear to use.
but in case thats not what you need; what you are describing is opposite of cross table load which is a generic load. see below
https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470

[sunny_talwar]

@dplr-rn what is the point of doing CrossTable if we have to use Generic later? Are we looking for ways to make the script un-optimized or make it look horribly difficult so that no one else can touch the app in the future 😉.

I think @srini is not able to explain his end goal very clearly. There might be easy way to handle what he wants, but without knowing the details makes it much more difficult to address the main problem.

[dplr-rn]

agree 🙂