Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thank you so much for being patience
I am still seeing blank values like this:
Below is my whole script:
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.
//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
Hi @Bharathi09 , Make the below 5 changes and see
1. CrossTable(Month, STD_LPM,10) to CrossTable(LPM_Month, STD_LPM,10)
2. Date(Date#("Year_" & Right(Month, 3), 'YYYYMMM'), 'YYYY-MM') as "Activity Date" to Date(Date#("Year_" & Right(LPM_Month, 3), 'YYYYMMM'), 'YYYY-MM') as "Activity Date".
3. CrossTable (Month, STD_LPW, 10) to CrossTable (LPW_Month, STD_LPW, 10)
4. Date(Date#("Year_" & Right(Month, 3), 'YYYYMMM'), 'YYYY-MM') as "Activity Date" to Date(Date#("Year_" & Right(LPW_Month, 3), 'YYYYMMM'), 'YYYY-MM') as "Activity Date"
5. Drop Fields STD_LPM, STD_LPW; to Drop Fields STD_LPM, STD_LPW, LPM_Month, LPW_Month;
In your script the 'Month' field is acting as a key. But the 'Month' field has different values in two tables.
I would recommend loading the data into memory first to use RESIDENT instead of loading from the XLSX twice. This worked for me:
Data:
NoConcatenate Load
MODE as mode,
AC,
JK,
Code,
CPO_IB,
STD1,
STD2,
Standard,
Volume,
YEAR as "Year_",
LPM_JAN, LPM_FEB, LPM_MAR, LPM_APR, LPM_MAY, LPM_JUN, LPM_JUL, LPM_AUG, LPM_SEP, LPM_OCT, LPM_NOV, LPM_DEC,
LPW_JAN, LPW_FEB, LPW_MAR, LPW_APR, LPW_MAY, LPW_JUN, LPW_JUL, LPW_AUG, LPW_SEP, LPW_OCT, LPW_NOV, LPW_DEC
Inline [
MODE, AC, JK, Code, CPO_IB, STD1, STD2, Standard, Volume, YEAR, LPM_JAN, LPM_FEB, LPM_MAR, LPM_APR, LPM_MAY, LPM_JUN, LPM_JUL, LPM_AUG, LPM_SEP, LPM_OCT, LPM_NOV, LPM_DEC, LPW_JAN, LPW_FEB, LPW_MAR, LPW_APR, LPW_MAY, LPW_JUN, LPW_JUL, LPW_AUG, LPW_SEP, LPW_OCT, LPW_NOV, LPW_DEC
MODE1, AC1, JK1, Code1, CPO_IB1, STD11, STD21, Standard1, Volume1, 2022, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212
MODE2, AC2, JK2, Code2, CPO_IB2, STD12, STD22, Standard2, Volume2, 2023, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224
];
CrossTableLPM:
CrossTable(MonthLPM, STD_LPM, 10) Load
mode, AC, JK, Code, CPO_IB, STD1, STD2, Standard, Volume, "Year_",
LPM_JAN, LPM_FEB, LPM_MAR, LPM_APR, LPM_MAY, LPM_JUN, LPM_JUL, LPM_AUG, LPM_SEP, LPM_OCT, LPM_NOV, LPM_DEC
Resident Data;
CrossTableLPW:
CrossTable(MonthLPW, STD_LPW, 10) Load
mode, AC, JK, Code, CPO_IB, STD1, STD2, Standard, Volume, "Year_",
LPW_JAN, LPW_FEB, LPW_MAR, LPW_APR, LPW_MAY, LPW_JUN, LPW_JUL, LPW_AUG, LPW_SEP, 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 MonthLPM, MonthLPW From STANDARD_COST;
I've seen this before is this in interview question?
Thank you so much for replying, your script is working mistly, but I can still see values like below
There can't be null values when there are values in std_lpw
With this the measure for sum(std_lpm) and sum(std_lpw) is increasing
Can you please help!!
Hi ,
check if this helps, i have reused the script provided by @LRuCelver
Hi @Bharathi09.
Could you share your code and potentially an excerpt of you data so we can check both?
Hello,
Input table:
Year | Mode | SCAC | AC | Code | JK | OG | IB | Vol | 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 | LRGR | 02459 - 02462 | 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 | LRGR | 02459 - 02462 | 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 | LRGR | 02459 - 02462 | 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 |
Output Table:
Mode | SCAC | AC | Code | JK | OG | IB | Volume | Standard | STD1 | STD2 | STD_LPM | STD_LPW | Activity Date |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 4 | 1/1/2023 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 412451 | 2.22 | 0.3375 | 0 | 2 | 5 | 1/1/2024 |
2459 | GR | Standards | 2459 | LRGR | 02459 - 02462 | 27458 J | 412451 | 1.87 | 0.9928 | 0 | 2 | 5 | 1/1/2024 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 4 | 2/1/2023 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 412451 | 2.22 | 0.3375 | 0 | 2 | 4 | 2/1/2024 |
2459 | GR | Standards | 2459 | LRGR | 02459 - 02462 | 27458 J | 412451 | 1.87 | 0.9928 | 0 | 2 | 4 | 2/1/2024 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 4 | 3/1/2023 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 3 | 4/1/2023 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 4 | 5/1/2023 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 4 | 6/1/2023 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 3 | 7/1/2023 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 4 | 8/1/2023 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 4 | 9/1/2023 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 5 | 10/1/2023 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 4 | 11/1/2023 |
2462 | GR | Standards | 2462 | LRGR | 02459 - 02462 | 27458 J | 406421 | 2.87 | 0.3243 | 0 | 2 | 3 | 12/1/2023 |
Hello,
Thanks for replying
It should be direct columns for STD_LPM and STD_LPW
there will also be sum(Volume) and other columns in frontend I need to apply as measure
So please help me and reply back
Thanks,
Hi @Bharathi09.
I've adjusted my previously posted script to run on the data you provided.
The resulting table has 36 rows (= 3 * 12, so should be correct) and STD_LPM and STD_LPW in the same rows:
Here is the adjusted script:
Data:
NoConcatenate Load
Year, Mode, SCAC, AC, Code, JK, OG, IB, Vol as Volume, Standard, STD1, STD2,
LPM_JAN, LPM_FEB, LPM_MAR, LPM_APR, LPM_MAY, LPM_JUN, LPM_JUL, LPM_AUG, LPM_SEPT as LPM_SEP, LPM_OCT, LPM_NOV, LPM_DEC,
LPW_JAN, LPW_FEB, LPW_MAR, LPW_APR, LPW_MAY, LPW_JUN, LPW_JUL, LPW_AUG, LPW_SEPT as LPW_SEP, LPW_OCT, LPW_NOV, LPW_DEC
Inline [
Year, Mode, SCAC, AC, Code, JK, OG, IB, Vol, 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, LRGR, 02459 - 02462, 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, LRGR, 02459 - 02462, 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, LRGR, 02459 - 02462, 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, 12) Load
Year, Mode, SCAC, AC, Code, JK, OG, IB, Volume, Standard, STD1, STD2,
LPM_JAN, LPM_FEB, LPM_MAR, LPM_APR, LPM_MAY, LPM_JUN, LPM_JUL, LPM_AUG, LPM_SEP, LPM_OCT, LPM_NOV, LPM_DEC
Resident Data;
CrossTableLPW:
CrossTable(MonthLPW, STD_LPW, 12) Load
Year, Mode, SCAC, AC, Code, JK, OG, IB, Volume, Standard, STD1, STD2,
LPW_JAN, LPW_FEB, LPW_MAR, LPW_APR, LPW_MAY, LPW_JUN, LPW_JUL, LPW_AUG, LPW_SEP,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;