Cross table working issue

[Bharathi09]

Hi all,

I have written my script as below, but in here My LPM values starts after LPW ends, For one activity date i wnat to show both LPM and LPW at same time not like values of LPM starts after LPW ends

CrossTable(Month, STD_LPM,10)
OLDDATA:
Load
"MODE" as mode,
AC,
JK,
Code,
CPO_IB,
STD1,
STD2,
"Standard",
"Volume",
"YEAR" as "Year_",
LPM_APR,
// LPW_APR,
LPM_AUG,
// LPW_AUG,
LPM_DEC,
// LPW_DEC,
LPM_FEB,
// LPW_FEB,
LPM_JAN,
// LPW_JAN,
LPM_JUL,
// LPW_JUL,
LPM_JUN,
// LPW_JUN,
LPM_MAR,
// LPW_MAR,
LPM_MAY,
// LPW_MAY,
LPM_NOV,
// LPW_NOV,
LPM_OCT,
// LPW_OCT,
LPM_SEPT
// LPW_SEPT
FROM 2024.XLSX

FINAL:
LOAD *,
STD_LPM as LPM,
Date(Date#("Year_" & Right(Month, 3), 'YYYYMMM'), 'YYYY-MM') as "Activity Date"
Resident OLDDATA;
Drop Table OLDDATA;
//Exit Script;

CrossTable (Month, STD_LPW, 10)

OLDDATA:
Load
"MODE" as mode,
AC,
JK,
Code,
CPO_IB,
STD1,
STD2,
"Standard",
"Volume",
"YEAR" as "Year_",
LPW_APR,
// LPW_APR,
LPW_AUG,
// LPW_AUG,
LPW_DEC,
// LPW_DEC,
LPW_FEB,
// LPW_FEB,
LPW_JAN,
// LPW_JAN,
LPW_JUL,
// LPW_JUL,
LPW_JUN,
// LPW_JUN,
LPW_MAR,
// LPW_MAR,
LPW_MAY,
// LPW_MAY,
LPW_NOV,
// LPW_NOV,
LPW_OCT,
// LPW_OCT,
LPW_SEPT
// LPW_SEPT
FROM 2024.XLSX

Join(FINAL)
Final2:
LOAD *,
STD_LPW as LPW,
Date(Date#("Year_" & Right(Month, 3), 'YYYYMMM'), 'YYYY-MM') as "Activity Date"
Resident OLDDATA;
Drop Fields STD_LPM, STD_LPW;

Drop Table OLDDATA;

Exit Script;

My o/p is coming like this, LPW starts after LPM ends
But I want to show both values at a time for each activity date

please help

[Bharathi09 · Accepted solution]

Thank you so much for being patience 
I am still seeing blank values like this:

Below is my whole script:

Data:

NoConcatenate

Load 

    "MODE" as mode,

    SCAC,

    JIT,

    Code,

    IB,

    STD1,

    STD1,

    "Standard",

    "Volume",

    "YEAR" as "Year_",

    LPW_JAN, 

    LPW_FEB,

    LPW_MAR,

    LPW_APR,

    LPW_MAY,

    LPW_JUN,

    LPW_JUL,

    LPW_AUG,

    LPW_SEPT,

    LPW_OCT,

    LPW_NOV,

    LPW_DEC,

    LPM_JAN, 

    LPM_FEB,

    LPM_MAR,

    LPM_APR,

    LPM_MAY,

    LPM_JUN,

    LPM_JUL,

    LPM_AUG,

    LPM_SEPT,

    LPM_OCT,

    LPM_NOV,

    LPM_DEC

FROM 2021.xlsx Path;

CrossTableLPM:

CrossTable(MonthLPM, STD_LPM, 10) Load

"Year_",

mode ,

    SCAC,

    JIT,

    Code,

    IB,

    STD1,

    STD2,

    "Standard",

    "Volume",

    LPM_JAN, LPM_FEB, LPM_MAR, LPM_APR, LPM_MAY, LPM_JUN, LPM_JUL, LPM_AUG, LPM_SEPT,

    LPM_OCT, LPM_NOV, LPM_DEC

Resident Data;

CrossTableLPW:

CrossTable(MonthLPW, STD_LPW, 10) Load

    "Year_",

mode ,

    SCAC,

    JIT,

    Code,

    IB,

    STD1,

    STD2,

    "Standard",

    "Volume",

LPW_JAN, LPW_FEB, LPW_MAR, LPW_APR, LPW_MAY, LPW_JUN, LPW_JUL, LPW_AUG, LPW_SEPT,

    LPW_OCT, LPW_NOV, LPW_DEC

Resident Data; 

Drop table Data;

STANDARD_COST:

NoConcatenate Load

    Date(Date#("Year_" & Right(MonthLPM, 3), 'YYYYMMM'),'YYYY-MM-DD') as "Activity Date",

    *

Resident CrossTableLPM;

Join Load

    Date(Date#("Year_" & Right(MonthLPW, 3), 'YYYYMMM'),'YYYY-MM-DD') as "Activity Date",

    *

Resident CrossTableLPW;

Drop Tables CrossTableLPM, CrossTableLPW;

Drop Fields "Year_", MonthLPM, MonthLPW From STANDARD_COST;

Exit Script;

[LRuCelver]

I've highlighted all 3 (technically 4) changes I've made to your code:

Data:
NoConcatenate
Load 
"MODE" as mode,
SCAC,
JIT,
Code,
IB,
STD1,
STD2, // You had STD1 here
"Standard",
"Volume",
"YEAR" as "Year_",
LPW_JAN, 
LPW_FEB,
LPW_MAR,
LPW_APR,
LPW_MAY,
LPW_JUN,
LPW_JUL,
LPW_AUG,
LPW_SEPT,
LPW_OCT,
LPW_NOV,
LPW_DEC,
LPM_JAN, 
LPM_FEB,
LPM_MAR,
LPM_APR,
LPM_MAY,
LPM_JUN,
LPM_JUL,
LPM_AUG,
LPM_SEPT,
LPM_OCT,
LPM_NOV,
LPM_DEC
Inline [ // Ignore this change. I don't have the file.
YEAR, MODE, SCAC, JIT, Code, IB, Volume, Standard, STD1, STD2, LPM_AUG, LPW_AUG, LPM_SEPT, LPW_SEPT, LPM_OCT, LPW_OCT, LPM_NOV, LPW_NOV, LPM_DEC, LPW_DEC, LPM_JAN, LPW_JAN, LPM_FEB, LPW_FEB, LPM_MAR, LPW_MAR, LPM_APR, LPW_APR, LPM_MAY, LPW_MAY, LPM_JUN, LPW_JUN, LPM_JUL, LPW_JUL
2023, 2462, GR, Standards, 2462, 27458 J, 406421, 2.87, 0.3243, 0, 2, 4, 2, 4, 2, 5, 2, 4, 2, 3, 2, 4, 2, 4, 2, 4, 2, 3, 2, 4, 2, 4, 2, 3
2024, 2462, GR, Standards, 2462, 27458 J, 412451, 2.22, 0.3375, 0, 2, 4, 2, 4, 2, 5, 2, 4, 2, 3, 2, 5, 2, 4, 2, 4, 2, 5, 2, 4, 2, 4, 2, 5
2024, 2459, GR, Standards, 2459, 27458 J, 412451, 1.87, 0.9928, 0, 2, 4, 2, 4, 2, 5, 2, 4, 2, 3, 2, 5, 2, 4, 2, 4, 2, 5, 2, 4, 2, 4, 2, 5
];


CrossTableLPM:
CrossTable(MonthLPM, STD_LPM, 10) Load
"Year_",
mode ,
SCAC,
JIT,
Code,
IB,
STD1,
STD2,
"Standard",
"Volume",
LPM_JAN, LPM_FEB, LPM_MAR, LPM_APR, LPM_MAY, LPM_JUN, LPM_JUL, LPM_AUG, LPM_SEPT as LPM_SEP, // All month names need the same formatting
LPM_OCT, LPM_NOV, LPM_DEC
Resident Data;

CrossTableLPW:
CrossTable(MonthLPW, STD_LPW, 10) Load
"Year_",
mode ,
SCAC,
JIT,
Code,
IB,
STD1,
STD2,
"Standard",
"Volume",
LPW_JAN, LPW_FEB, LPW_MAR, LPW_APR, LPW_MAY, LPW_JUN, LPW_JUL, LPW_AUG, LPW_SEPT as LPW_SEP, // All month names need the same formatting
LPW_OCT, LPW_NOV, LPW_DEC
Resident Data; 
Drop table Data;



STANDARD_COST:
NoConcatenate Load
Date(Date#("Year_" & Right(MonthLPM, 3), 'YYYYMMM'),'YYYY-MM-DD') as "Activity Date",
*
Resident CrossTableLPM;

Join Load
Date(Date#("Year_" & Right(MonthLPW, 3), 'YYYYMMM'),'YYYY-MM-DD') as "Activity Date",
*
Resident CrossTableLPW;

Drop Tables CrossTableLPM, CrossTableLPW;
Drop Fields "Year_", MonthLPM, MonthLPW From STANDARD_COST;
Exit Script;

[Sivapriya_d · Accepted solution]

Hi, 
Please try this Script if it suits you. I have segregated the measures based on my assumption, Please get it updated as per your actual data.

Fact:

Load

Hash128(Year&Mode) as Key,

Vol as Volume,

Standard,

STD1,

STD2

FROM

Input.xlsx

(ooxml, embedded labels, table is Sheet1);

Temp:

CrossTable(Month,STD,8)

LOAD Year, 

     Mode, 

     SCAC, 

     AC, 

     Code, 

     JK, 

     OG, 

     IB, 

     LPM_AUG, 

     LPW_AUG, 

     LPM_SEPT as LPM_SEP, 

     LPW_SEPT as LPW_SEP, 

     LPM_OCT, 

     LPW_OCT, 

     LPM_NOV, 

     LPW_NOV, 

     LPM_DEC, 

     LPW_DEC, 

     LPM_JAN, 

     LPW_JAN, 

     LPM_FEB, 

     LPW_FEB, 

     LPM_MAR, 

     LPW_MAR, 

     LPM_APR, 

     LPW_APR, 

     LPM_MAY, 

     LPW_MAY, 

     LPM_JUN, 

     LPW_JUN, 

     LPM_JUL, 

     LPW_JUL

FROM

Input.xlsx

(ooxml, embedded labels, table is Sheet1);

Data:

Load *,

Left(Month,3) as STDList,

Hash128(Year&Mode) as Key,

Date(Date#(Year & Right(Month, 3), 'YYYYMMM'),'YYYY-MM-DD') as "Activity Date"

Resident Temp;

DROP Table Temp;

//End of The Script

 In Front end I have used below for STD_LPM,STD_LPW
STD_LPM--> Sum({<STDList={'LPM'}>}STD)
STD_LPW-->Sum({<STDList={'LPW'}>}STD)

With the above script i am getting below result 

[Bharathi09]

I have one more question, it'll be really helpful if you make a try on it

After making cross join the values for one column is duplicating and when doing sum(col), it's giving more values
I have to do  now as
Original table:
Orig,
Lane
dest,
Cost,
Vol,
STD1,
STD2,
Year,
'std' as "std_s"
LPM_JAN to LPM_DEC,
LPW_JAN to LPW_DEC


Dup table:
Orig,
dest,
Cost,
Vol,
STD1,
STD2,
LPM,
LPW,
'std' as "std_s"
Ship date

i want to create key and do association between two tables and remove duplicates in second table
I don't know how to achieve it, can you please help me!!!

It's example below:
Original table:

Lane	origin	dest	Year	STD2	STD1	Standards	cost	LPM_JAN	LPM_FEB	LPW_JAN	LPW_FEB
101	2J	20K	2023	2	8	Y	1200	5	7	8	9
102	2K	20S	2023	5	7	Y	1300	6	4	2	3

Dup table:

Lane	origin	dest	Standards	Split cost	Ship date	LPM	LPW	STD1	STD2
101	2J	20k	Y	1200	2023-01	5	8	2	8
101	2J	20k	Y	1300	2023-01	6	2	5	7
102	2K	20S	Y	1200	2023-02	7	9	2	8
102	2K	20S	Y	1300	2023-02	4	3	5	7

please reply back