Cross table logic

Bharathi09 · ‎2024-02-08

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;

LRuCelver · ‎2024-02-09

Data:
NoConcatenate Load * Inline [
	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
	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
	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, FieldLPM, 1) Load
	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, FieldLPW, 1) Load
	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(Month, 3), 'YYYYMMM'),'YYYY-MM-DD') as "Activity Date",
    FieldLPM
Resident CrossTableLPM;

Join Load
	Date(Date#(Year & Right(Month, 3), 'YYYYMMM'),'YYYY-MM-DD') as "Activity Date",
    FieldLPW
Resident CrossTableLPW;

Drop Tables CrossTableLPM, CrossTableLPW;

Bharathi09 · ‎2024-02-09

Thanks a lot for replying,

OldData:
LOAD
"MODE",
Code,
IB,
"YEAR" as Year,
STD,
STD,
"CPU",
"Volume",
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 2021.xlsx;

CrossTableLPM:
CrossTable(Month, FieldLPM, 1)
Load Year, 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 OldData;

CrossTableLPW:
CrossTable(Month, FieldLPW, 1)
Load 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
Resident OldData;
Drop Table OldData;

STANDARD_COST:
NoConcatenate
Load Date(Date#(Year & Right(Month, 3), 'YYYYMMM'),'YYYY-MM') as "Activity Date",
FieldLPM
Resident CrossTableLPM;

Join
Load
Date(Date#(Year & Right(Month, 3), 'YYYYMMM'),'YYYY-MM') as "Activity Date",
FieldLPW
Resident CrossTableLPW;
Drop Tables CrossTableLPM, CrossTableLPW;

This above logic worked, but in my table I can see only Activity Date, FieldLpm and FieldLPW,
But i wnat to see all fields in Old data table other than lpm jan to dec and lpw jan to december
Can you pls hlp

Please reply back
Thanks

LRuCelver · ‎2024-02-12

HI @Bharathi09. I adjusted the code a bit. You can change the number of fields you would like to keep by adding them to the script and adjusting the number in the crosstable load. It is important that all fields that are a part of the crosstable are loaded last. If you already have them in that order, you can replace all the fields with *. But if your fields won't change it doesn't hurt to write them all in the script to make sure the order stays the same.

Data:
NoConcatenate Load * Inline [
	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, ExtraField1, ExtraField2, ExtraField3
	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,     Hello11,     Hello12,     Hello13
	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,     Hello21,     Hello22,     Hello23
];


CrossTableLPM:
CrossTable(MonthLPM, FieldLPM, 4) Load
	Year, ExtraField1, ExtraField2, ExtraField3, // The first 4 fields will remain
    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, FieldLPW, 4) Load
    Year, ExtraField1, ExtraField2, ExtraField3, // The first 4 fields will remain
	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;

Developers

expression