Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM')) as [Month] is not working fine

[srini]

when I use the logic in the script with one of my other report, I am getting different out put. code: Test1: Load "Cycle Year", "Cyclename", // "Governance Group", // "Product Line", // "GPDM Portfolio", // "GPDM Programme", // "POB ID", // "POB Name", // "BP Priority", "BP ID (for filtering)", // "BP Name", Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM')) as [Month], Num(Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM'))) as [MonthNum], Units out put:

Thanks in advance!

[sunny_talwar]

Are you just loading a single file or do you have multiple files getting loaded?

[srini]

single file bro!

[sunny_talwar]

So, essentially you are seeing some of the values getting interpreted correctly, while others are not? Can you share the script you are using?

[srini]

here is the script Test1: Load "Cycle Year", "Cyclename", // "Governance Group", // "Product Line", // "GPDM Portfolio", // "GPDM Programme", // "POB ID", // "POB Name", // "BP Priority", "BP ID (for filtering)", // "BP Name", Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM')) as [Month], Num(Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM'))) as [MonthNum], Units Resident value; Drop Table value;

[srini]

Bro, I have sent you some message. Please have a look.

[sunny_talwar]

This script doesn't give me much info. May be share the app or logfile.

[sunny_talwar]

I looked at your script from the other thread and it seems that you are not dropping your tables which is causing the issue...Also make sure to add NoConcatenate after Test1 table... try this

Test1:
NoConcatenate
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 Test;
Drop Table Test;
 
Test2:
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"
Resident Test1;
Drop Table Test1;

[sunny_talwar]

Just saw that you are creating MonthNum in Test1, so NoConcatenate is not needed, but doesn't harm having it in there.

[sunny_talwar]

You can fine tune your script to be just this

Data:
CrossTable(Month,Units,2)
LOAD Country,
    Category,
    "M01 Units",
    "M02 Units",
    "M03 Units",
    "M04 Units",
    "M05 Units",
    "M06 Units",
    "M07 Units",
    "M08 Units",
    "M09 Units",
    "M10 Units",
    "M11 Units",
    "M12 Units"
FROM [lib://model/Model file (3).xlsx]
(ooxml, embedded labels, header is 3 lines, table is source);
   
Test1:
LOAD *,
     If(Category = 'ACTUAL' and MonthNum <= Num(Month(Today())), Units) as "YTD Actuals",
     If(Category = 'FORECAST' and MonthNum <= Num(Month(Today())), Units) as "YTD FORECAST";
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 Data;