Solved: Re: Cross table working issue - Qlik Community

Bharathi09 · ‎2024-02-12

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 · ‎2024-02-13

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,

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

View solution in original post

Sivapriya_d · ‎2024-02-13

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

View solution in original post

raju_insights · ‎2024-02-12

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.

LRuCelver · ‎2024-02-12

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;

vinieme12 · ‎2024-02-12

I've seen this before is this in interview question?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Bharathi09 · ‎2024-02-12

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!!

Sivapriya_d · ‎2024-02-12

Hi ,
check if this helps, i have reused the script provided by @LRuCelver

Data:

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(Month, STD, 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(Month, STD, 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:

Load *,

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

Left(Month,3) as Std_Var

Resident CrossTableLPM;

Drop Table CrossTableLPM;

*****In the front end while calculating Sum, Please define like this
LPM- >Sum({<Std_Var={'LPM'}>}STD)
LPW- > =Sum({<Std_Var={'LPW'}>}STD)

Thanks,

LRuCelver · ‎2024-02-12

Hi @Bharathi09.

Could you share your code and potentially an excerpt of you data so we can check both?

Bharathi09 · ‎2024-02-12

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

Bharathi09 · ‎2024-02-13

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,

LRuCelver · ‎2024-02-13

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;

Cross table working issue

Data Load Editor

Developers

expression

Script