Cross Table logic

[Mounika9]

Hi all, 
I have implemented a logic in QV, to pivot LPM_JAN TO LPM_DEC in STD_LPM and LPW_JAN to LPW_DEC In STD_LPW, but my logic is very large, can anyone helm me to optimize using Crosstable

INPUT DATA:
load * from 2021.xlsx

STANDARD_COST_TABLE:

LOAD *,

Date(Monthstart(Year & '-01-01')) as [Date_Field],

'LPM_JAN' as Field,

'LPW_JAN' as FieldLPW,

LPM_JAN as STD_LPM,

LPW_JAN as STD_LPW

Resident [INPUT DATA];
Concatenate (STANDARD_COST_TABLE)

LOAD *,

Date(Monthstart(Year & '-02-01')) as [Date_Field],

'LPM_FEB' as Field,

'LPW_FEB' as FieldLPW,

LPM_FEB as STD_LPM,

LPW_FEB as STD_LPW

Resident [INPUT DATA];

Concatenate (STANDARD_COST_TABLE)

LOAD *,

Date(Monthstart(Year & '-03-01')) as [Date_Field],

'LPM_MAR' as Field,

'LPW_MAR' as FieldLPW,

LPM_MAR as STD_LPM,

LPW_MAR as STD_LPW

Resident [INPUT DATA];

Concatenate (STANDARD_COST_TABLE)

LOAD *,

Date(Monthstart(Year & '-04-01')) as [Date_Field],

'LPM_APR' as Field,

'LPW_APR' as FieldLPW,

LPM_APR as STD_LPM,

LPW_APR as STD_LPW

Resident [INPUT DATA];

Concatenate (STANDARD_COST_TABLE)

LOAD *,

Date(Monthstart(Year & '-05-01')) as [Date_Field],

'LPM_MAY' as Field,

'LPW_MAY' as FieldLPW,

LPM_MAY as STD_LPM,

LPW_MAY as STD_LPW

Resident [INPUT DATA];

Concatenate (STANDARD_COST_TABLE)

LOAD *,

Date(Monthstart(Year & '-06-01'))as [Date_Field],

'LPM_JUN' as Field,

'LPW_JUN' as FieldLPW,

LPM_JUN as STD_LPM,

LPW_JUN as STD_LPW

Resident [INPUT DATA];

Concatenate (STANDARD_COST_TABLE)

LOAD *,

Date(Monthstart(Year & '-07-01')) as [Date_Field],

'LPM_JUL' as Field,

'LPW_JUL' as FieldLPW,

LPM_JUL as STD_LPM,

LPW_JUL as STD_LPW

Resident [INPUT DATA];

Concatenate (STANDARD_COST_TABLE)

LOAD *,

Date(Monthstart(Year & '-08-01')) as [Date_Field],

'LPM_AUG' as Field,

'LPW_AUG' as FieldLPW,

LPM_AUG as STD_LPM,

LPW_AUG as STD_LPW

Resident [INPUT DATA];

Concatenate (STANDARD_COST_TABLE)

LOAD *,

Date(Monthstart( Year & '-09-01')) as [Date_Field],

'LPM_SEPT' as Field,

'LPW_SEPT' as FieldLPW,

LPM_SEPT as STD_LPM,

LPW_SEPT as STD_LPW

Resident [INPUT DATA];

Concatenate (STANDARD_COST_TABLE)

LOAD *,

Date(Monthstart(Year & '-10-01')) as [Date_Field],

'LPM_OCT' as Field,

'LPW_OCT' as FieldLPW,

LPM_OCT as STD_LPM,

LPW_OCT as STD_LPW

Resident [INPUT DATA];

Concatenate (STANDARD_COST_TABLE)

LOAD *,

Date(Monthstart(Year & '-11-01' ))as [Date_Field],

'LPM_NOV' as Field,

'LPW_NOV' as FieldLPW,

LPM_NOV as STD_LPM,

LPW_NOV as STD_LPW

Resident [INPUT DATA];

Concatenate (STANDARD_COST_TABLE)

LOAD *,

Date(MonthStart(Year & '-12-01')) as [Date_Field],

'LPM_DEC' as Field,

'LPW_DEC' as FieldLPW,

LPM_DEC as STD_LPM,

LPW_DEC as STD_LPW

Resident [INPUT DATA];

DROP TABLE [INPUT DATA];

Drop Fields Field, FieldLPW, Year, 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,
LPM_AUG, LPW_AUG, LPM_SEPT, LPW_SEPT, LPM_OCT, LPW_OCT, LPM_NOV, LPW_NOV, LPM_DEC, LPW_DEC;

NoConcatenate

STANDARD_COST:
LOAD *,
date("Date_Field",'YYYY-MM-DD') as [Activity Date]
RESIDENT STANDARD_COST_TABLE
WHERE Year(date("Date_Field",'YYYY-MM-DD')) >= '$(vYear)' and "Date_Field" < Today();

DROP TABLE STANDARD_COST_TABLE;
STORE STANDARD_COST INTO [$(vQVDPath)\STD 2021.Qvd](qvd);

//DROP TABLE STANDARD_COST;

[NellyAcko]

What do you get when you use this ? The date field may need adding in  

INPUT DATA:
load * from 2021.xlsx

STANDARD_COST_TABLE:

Crosstable (Details, Data, 2) Load *

Resident [INPUT DATA];

[Mounika9]

Hi,
Firstly the output was like below

Year	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	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	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	2	4	2	4	2	5	2	4	2	3	2	5	2	4	2	4	2	5	2	4	2	4	2	5

The output I need in pivotted way should be like below

STD_LPM	STD_LPW	Activity Date
2	4	1/1/2023
2	5	1/1/2024
2	5	1/1/2024
2	4	2/1/2023
2	4	2/1/2024
2	4	2/1/2024
2	4	3/1/2023
2	3	4/1/2023
2	4	5/1/2023
2	4	6/1/2023
2	3	7/1/2023
2	4	8/1/2023
2	4	9/1/2023
2	5	10/1/2023
2	4	11/1/2023
2	3	12/1/2023

[vinieme12]

try below

CrossTable(Field,Values,1)

temp:
load * from 2021.xlsx;

temp2:

Load 

Year

,if(Index(Field,'LPM'),Values) as LPM

,if(Index(Field,'LPW'),Values) as LPW

,MonthStart(Date#(Year&Subfield(Field,'_',2),'YYYYMMM')) as Datefield

Resident temp;

drop table temp;

Final:

Load 

Datefield, Sum(LPM) as LPM , sum(LPW) as LPW

Resident temp2

Group by Datefield;

drop table temp2;

[LRuCelver]

This post seems to be a duplicate. I posted my solution in the other post: Cross table logic - Qlik Community - 2416864

[vinieme12]

seems like an interview question