topic Re: Cross table logic in Visualization and Usability

Cross table logic

Bharathi09 — Fri, 09 Feb 2024 03:44:02 GMT

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;

Re: Cross table logic

LRuCelver — Fri, 09 Feb 2024 07:30:52 GMT

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;

Re: Cross table logic

Bharathi09 — Fri, 09 Feb 2024 08:39:49 GMT

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

Re: Cross table logic

LRuCelver — Mon, 12 Feb 2024 06:28:13 GMT

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;