topic Re: Data Modelling Challenge in QlikView

Data Modelling Challenge

nickjose7 — Wed, 25 Nov 2020 16:16:04 GMT

antoniotiman‌ franky_h79‌ pradosh_thakur‌ its.anandrjs‌

Dear Frank/Antonio/Pradosh/Anand,

I have been taking help from all of you in the past week for this Data Model. Your Solutions have been really helpful and I wish to thank you all.

However I am somehow stuck in this and it has become a bit messy. Instead of sharing one by one problems I am sharing my sample data as well the requirement.

Would really appreciate if you could try your hands at this Data Model and help me out. I have this product release in next 22 hours.

Counting on your kind support.

Attached excel contains the details of requirement as well as the sample data tables. First sheet of the excel tells what is to be extracted and what to be created. Rest of the worksheets are actual tables to be loaded.

Many Thanks

Nick

Message was edited by: Nick Jose Revised the sample data in tables: 'Aug'17 end result' and 'PSI Sep'17'

Re: Data Modelling Challenge

MK9885 — Tue, 10 Oct 2017 19:10:34 GMT

The field names in your XL file has sub field names... this would be really challenging.

Cus Embed lines would eliminate 1 or 2 line or maybe 3 but it wouldn't make sense here.

suggestion, concatenate the tables with same fields, like BP and IBP

Aug-2017 and Sep 2017 etc

they have same field names.

Plus Aug'17 or Mar'17 would also be hard to convert into months or dates. Rather it should be Aug-2017 or Aug-17.

Maybe others might have different approach on this, I gave you my opinion.

Thanks.

Re: Data Modelling Challenge

vvira1316 — Tue, 10 Oct 2017 23:18:53 GMT

Hi Nick,

Please see if following will help, if so we can expand it to other worksheets, your exclude, include criterias

NoConcatenate
SaleRegisterReadTmp:
LOAD
MonthStart(Date#(Left(Month, 3) & '/01/' & Right(Month, 2),'MMM/DD/YY')) & Item as TblJoinkey,
MonthStart(Date#(Left(Month, 3) & '/01/' & Right(Month, 2),'MMM/DD/YY')) as Link_Date,
[Bill Type],
[BP Group],
[Product Group],
[Mat. Descrip],
Division,
Item as Link_Model,
Quantity,
[Sales Organization],
[Distribution Channel],
[Gross Price Amt(INR)]
FROM
[..\Data\Final.xlsx]
(ooxml, embedded labels, table is [Sale Register])
//Where Match(Division, 'CSD' , 'SSD', 'Mobile') // Filtering can be done later or while loading
;

NoConcatenate
BPReadTmp:
LOAD Models as Link_Model,
[Sale Qty],
[Sale Value],
COGS,
[Sale Qty1],
[Sale Value1],
COGS1,
[Sale Qty2],
[Sale Value2],
COGS2,
[Sale Qty3],
[Sale Value3],
COGS3,
[Sale Qty4],
[Sale Value4],
COGS4,
[Sale Qty5],
[Sale Value5],
COGS5,
[Sale Qty6],
[Sale Value6],
COGS6,
[Sale Qty7],
[Sale Value7],
COGS7,
[Sale Qty8],
[Sale Value8],
COGS8,
[Sale Qty9],
[Sale Value9],
COGS9,
[Sale Qty10],
[Sale Value10],
COGS10,
[Sale Qty11],
[Sale Value11],
COGS11,
[Sale Qty12],
[Sale Value12],
COGS12,
[Sale Qty13],
[Sale Value13],
COGS13,
[Sale Qty14],
[Sale Value14],
COGS14,
[Sale Qty15],
[Sale Value15],
COGS15,
[Sale Qty16],
[Sale Value16],
COGS16,
[Sale Qty17],
[Sale Value17],
COGS17,
[Sale Qty18],
[Sale Value18],
COGS18,
[Sale Qty19],
[Sale Value19],
COGS19,
[Sale Qty20],
[Sale Value20],
COGS20,
[Sale Qty21],
[Sale Value21],
COGS21
FROM
[..\Data\Final.xlsx]
(ooxml, embedded labels, header is 1 lines, table is BP);

NoConcatenate
Table_BP:
LOAD MonthStart(Date#('Apr/01/17', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty10] as Sale_Quantity_BP,
[Sale Value10] as Sale_Value_BP,
COGS10 as COGS_BP
Resident BPReadTmp;
LOAD MonthStart(Date#('May/01/17', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty11] as Sale_Quantity_BP,
[Sale Value11] as Sale_Value_BP,
COGS11 as COGS_BP
Resident BPReadTmp;
LOAD MonthStart(Date#('Jun/01/17', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty12] as Sale_Quantity_BP,
[Sale Value12] as Sale_Value_BP,
COGS12 as COGS_BP
Resident BPReadTmp;
LOAD MonthStart(Date#('Jul/01/17', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty13] as Sale_Quantity_BP,
[Sale Value13] as Sale_Value_BP,
COGS13 as COGS_BP
Resident BPReadTmp;
LOAD MonthStart(Date#('Aug/01/17', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty14] as Sale_Quantity_BP,
[Sale Value14] as Sale_Value_BP,
COGS14 as COGS_BP
Resident BPReadTmp;
LOAD MonthStart(Date#('Sep/01/17', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty15] as Sale_Quantity_BP,
[Sale Value15] as Sale_Value_BP,
COGS15 as COGS_BP
Resident BPReadTmp;
LOAD MonthStart(Date#('Oct/01/17', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty16] as Sale_Quantity_BP,
[Sale Value16] as Sale_Value_BP,
COGS16 as COGS_BP
Resident BPReadTmp;
LOAD MonthStart(Date#('Nov/01/17', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty17] as Sale_Quantity_BP,
[Sale Value17] as Sale_Value_BP,
COGS17 as COGS_BP
Resident BPReadTmp;
LOAD MonthStart(Date#('Dec/01/17', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty18] as Sale_Quantity_BP,
[Sale Value18] as Sale_Value_BP,
COGS18 as COGS_BP
Resident BPReadTmp;
LOAD MonthStart(Date#('Jan/01/18', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty19] as Sale_Quantity_BP,
[Sale Value19] as Sale_Value_BP,
COGS19 as COGS_BP
Resident BPReadTmp;
LOAD MonthStart(Date#('Feb/01/18', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty20] as Sale_Quantity_BP,
[Sale Value20] as Sale_Value_BP,
COGS20 as COGS_BP
Resident BPReadTmp;
LOAD MonthStart(Date#('Mar/01/18', 'MMM/DD/YY')) & Link_Model as TblJoinkey,
Link_Model as Link_Model_BP,
[Sale Qty21] as Sale_Quantity_BP,
[Sale Value21] as Sale_Value_BP,
COGS21 as COGS_BP
Resident BPReadTmp;

DROP Table BPReadTmp;

Re: Data Modelling Challenge

nickjose7 — Wed, 11 Oct 2017 10:45:51 GMT

Hi Vijay,

This appears to be fine. Kindly share the rest of the solution.

Many Thanks

Nick

Re: Data Modelling Challenge

vvira1316 — Wed, 11 Oct 2017 16:27:17 GMT

Hi Nick,

I'm glad to know that it is in the right direction. I hope it gave you a direction to continue with your requirements.

I would like to keep you informed that it will take a while for me to continue expanding on your detail requirements as I have my own work and I'll be only able to spend time when I'll have an opportunity in between my tasks or when I've free time. I would encourage you to leverage the file I've attached to include other data in a similar way.

If you get stuck then please feel free to ask for help.

BR,

Vijay

Re: Data Modelling Challenge

vvira1316 — Wed, 11 Oct 2017 18:11:47 GMT

Hi Nick,

Here is the structure . I would clean it up and add criteria or use criteria in calculation. I would opt not create the aggregate table but construct formulas for dashboard need in the chart. May be variables for reuse.

Sales by Model Aug'17 - CSD looks like aggregate table.

From all Tables I would drop certain values as can be seen from screen shot below

TblJoinKeyValuesToBeDropped

let me know if you have any questions or need more help

Re: Data Modelling Challenge

vvira1316 — Thu, 12 Oct 2017 15:56:08 GMT

Hi Nick,

I hope you were able to make progress with information I have provided. Let us know if any further help is needed.

BR,

Vijay

Re: Data Modelling Challenge

nickjose7 — Thu, 12 Oct 2017 17:25:39 GMT

Hi Vijay,

Many thanks for your help. However we still need to make certain changes as ER table should give numeric values not dates in Quantity or Value field. Plus can't we make Link_Model as the primary key instead of TblJoinkey?

I have revised the data of PSI table and Aug'17 ER table. Would really appreciate if you could use this data and think on the changes that brings out the desired results.

The idea is to get the following:

Sale Values and Quantities of 4 types: Actual,BP,IBP and Forecast.

Where all these will be for current month i.e. Aug'17 in our case, the Forecast has to be considered for next 6 months i.e From Sep'17 till Feb'18.

Have attached the revised data to the original post and looking forward to your response!!

Thanks

Nick

Re: Data Modelling Challenge

vvira1316 — Thu, 12 Oct 2017 17:39:51 GMT

HI Nick,

I'm taking a look at your new data file. Will use that. Only reason I joined them was to avoid Synthetic Key, I think we can create what you are looking for. Let me review it again to see how we can modify it.

Will update you.

Is Link_Date and Link_Model have to be available in our tables? Can Link_Date be renamed in other tables so there won't be two common fields (Namely Link_Model and Link_Date) in our tables.

BR,

Vijay

Re: Data Modelling Challenge

nickjose7 — Fri, 13 Oct 2017 07:42:40 GMT

Thanks Vijay.

There is no problem if you are renaming these two fields as long as our requirement is fulfilled which is:

Link_Dates are to be used to link with the Master Calendar.

Link_Model: All Quantities and Values are to be seen Model wise.

Looking forward to your updates.

Nick

Re: Data Modelling Challenge

vvira1316 — Mon, 16 Oct 2017 00:07:13 GMT

Hi Nick,

How about following....

You can modify script to add your selection inclusion/exclusion or you can do that in table calculation

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

NoConcatenate

SaleRegister:

LOAD

MonthStart(Date#(Left(Month, 3) & '/01/20' & Right(Month, 2),'MMM/DD/YY')) & Chr(59) & Item as TblJoinkey,

MonthStart(Date#(Left(Month, 3) & '/01/20' & Right(Month, 2),'MMM/DD/YY')) as Sale_Link_Date,

[Bill Type],

[BP Group],

[Product Group],

[Mat. Descrip],

Division,

Item as Sale_Link_Model,

Quantity,

[Sales Organization],

[Distribution Channel],

[Gross Price Amt(INR)]

FROM

[Final.xlsx]

(ooxml, embedded labels, table is [Sale Register])

//Where Match(Division, 'CSD' , 'SSD', 'Mobile') // Filtering can be done later or while loading

;

NoConcatenate

BPReadTmp:

LOAD Models as Link_Model,

[Sale Qty],

[Sale Value],

COGS,

[Sale Qty1],

[Sale Value1],

COGS1,

[Sale Qty2],

[Sale Value2],

COGS2,

[Sale Qty3],

[Sale Value3],

COGS3,

[Sale Qty4],

[Sale Value4],

COGS4,

[Sale Qty5],

[Sale Value5],

COGS5,

[Sale Qty6],

[Sale Value6],

COGS6,

[Sale Qty7],

[Sale Value7],

COGS7,

[Sale Qty8],

[Sale Value8],

COGS8,

[Sale Qty9],

[Sale Value9],

COGS9,

[Sale Qty10],

[Sale Value10],

COGS10,

[Sale Qty11],

[Sale Value11],

COGS11,

[Sale Qty12],

[Sale Value12],

COGS12,

[Sale Qty13],

[Sale Value13],

COGS13,

[Sale Qty14],

[Sale Value14],

COGS14,

[Sale Qty15],

[Sale Value15],

COGS15,

[Sale Qty16],

[Sale Value16],

COGS16,

[Sale Qty17],

[Sale Value17],

COGS17,

[Sale Qty18],

[Sale Value18],

COGS18,

[Sale Qty19],

[Sale Value19],

COGS19,

[Sale Qty20],

[Sale Value20],

COGS20,

[Sale Qty21],

[Sale Value21],

COGS21

FROM

[Final.xlsx]

(ooxml, embedded labels, header is 1 lines, table is BP);

NoConcatenate

IBPReadTmp:

LOAD Models as Link_Model,

[Sale Qty],

[Sale Value],

COGS,

[Sale Qty1],

[Sale Value1],

COGS1,

[Sale Qty2],

[Sale Value2],

COGS2,

[Sale Qty3],

[Sale Value3],

COGS3,

[Sale Qty4],

[Sale Value4],

COGS4,

[Sale Qty5],

[Sale Value5],

COGS5,

[Sale Qty6],

[Sale Value6],

COGS6,

[Sale Qty7],

[Sale Value7],

COGS7,

[Sale Qty8],

[Sale Value8],

COGS8,

[Sale Qty9],

[Sale Value9],

COGS9,

[Sale Qty10],

[Sale Value10],

COGS10,

[Sale Qty11],

[Sale Value11],

COGS11,

[Sale Qty12],

[Sale Value12],

COGS12,

[Sale Qty13],

[Sale Value13],

COGS13,

[Sale Qty14],

[Sale Value14],

COGS14,

[Sale Qty15],

[Sale Value15],

COGS15,

[Sale Qty16],

[Sale Value16],

COGS16,

[Sale Qty17],

[Sale Value17],

COGS17,

[Sale Qty18],

[Sale Value18],

COGS18,

[Sale Qty19],

[Sale Value19],

COGS19,

[Sale Qty20],

[Sale Value20],

COGS20,

[Sale Qty21],

[Sale Value21],

COGS21,

[Sale Qty22],

[Sale Value22],

COGS22,

[Sale Qty23],

[Sale Value23],

COGS23,

[Sale Qty24],

[Sale Value24],

COGS24,

[Sale Qty25],

[Sale Value25],

COGS25,

[Sale Qty26],

[Sale Value26],

COGS26,

[Sale Qty27],

[Sale Value27],

COGS27,

[Sale Qty28],

[Sale Value28],

COGS28,

[Sale Qty29],

[Sale Value29],

COGS29,

[Sale Qty30],

[Sale Value30],

COGS30,

[Sale Qty31],

[Sale Value31],

COGS31,

[Sale Qty32],

[Sale Value32],

COGS32,

[Sale Qty33],

[Sale Value33],

COGS33

FROM

Final.xlsx

(ooxml, embedded labels, header is 1 lines, table is IBP);

NoConcatenate

PSIReadTmp1:

LOAD

[Internal Model] as Link_Model,

Num([42995]) as Sep17Qty,

Num([Sep-17 ($)]) as Sep17Value,

Num([43025]) as Oct17Qty,

Num([Oct-17 ($)]) as Oct17Value,

Num([43056]) as Nov17Qty,

Num([Nov-17 ($)]) as Nov17Value,

Num([43086]) as Dec17Qty,

Num([Dec-17 ($)]) as Dec17Value,

Num([42753]) as Jan18Qty,

Num([Jan-18 ($)]) as Jan18Value,

Num([42784]) as Feb18Qty,

Num([Feb-18 ($)]) as Feb18Value

FROM

Final.xlsx

(ooxml, embedded labels, table is [PSI Sep'17])

where Type='S';

NoConcatenate

PSIReadTmp2:

LOAD

[Internal Model] as Link_Model,

Num([Sep-17 ($)]) as Sep17COGS,

Num([Oct-17 ($)]) as Oct17COGS,

Num([Nov-17 ($)]) as Nov17COGS,

Num([Dec-17 ($)]) as Dec17COGS,

Num([Jan-18 ($)]) as Jan18COGS,

Num([Feb-18 ($)]) as Feb18COGS

FROM

Final.xlsx

(ooxml, embedded labels, table is [PSI Sep'17])

where Type='COGS';

LET vNoOfRows = NoOfRows('PSIReadTmp1');

FOR i = 0 to $(vNoOfRows) - 1

LET vLink_Model = Peek('Link_Model', i, 'PSIReadTmp1');

LET vSep17Qty = Peek('Sep17Qty', i, 'PSIReadTmp1');

LET vOct17Qty = Peek('Oct17Qty', i, 'PSIReadTmp1');

LET vNov17Qty = Peek('Nov17Qty', i, 'PSIReadTmp1');

LET vDec17Qty = Peek('Dec17Qty', i, 'PSIReadTmp1');

LET vJan18Qty = Peek('Jan18Qty', i, 'PSIReadTmp1');

LET vFeb18Qty = Peek('Feb18Qty', i, 'PSIReadTmp1');

LET vSep17Value = Peek('Sep17Value', i, 'PSIReadTmp1');

LET vOct17Value = Peek('Oct17Value', i, 'PSIReadTmp1');

LET vNov17Value = Peek('Nov17Value', i, 'PSIReadTmp1');

LET vDec17Value = Peek('Dec17Value', i, 'PSIReadTmp1');

LET vJan18Value = Peek('Jan18Value', i, 'PSIReadTmp1');

LET vFeb18Value = Peek('Feb18Value', i, 'PSIReadTmp1');

LET vSep17COGS = Peek('Sep17COGS', i, 'PSIReadTmp2');

LET vOct17COGS = Peek('Oct17COGS', i, 'PSIReadTmp2');

LET vNov17COGS = Peek('Nov17COGS', i, 'PSIReadTmp2');

LET vDec17COGS = Peek('Dec17COGS', i, 'PSIReadTmp2');

LET vJan18COGS = Peek('Jan18COGS', i, 'PSIReadTmp2');

LET vFeb18COGS = Peek('Feb18COGS', i, 'PSIReadTmp2');

PSIReadTmp:

LOAD * Inline

[

"Link_Model", "Sep17Qty", "Oct17Qty", "Nov17Qty", "Dec17Qty", "Jan18Qty", "Feb18Qty", "Sep17Value", "Oct17Value", "Nov17Value", "Dec17Value", "Jan18Value", "Feb18Value", "Sep17COGS", "Oct17COGS", "Nov17COGS", "Dec17COGS", "Jan18COGS", "Feb18COGS"

$(vLink_Model), $(vSep17Qty), $(vOct17Qty), $(vNov17Qty), $(vDec17Qty), $(vJan18Qty), $(vFeb18Qty), $(vSep17Value), $(vOct17Value), $(vNov17Value), $(vDec17Value), $(vJan18Value), $(vFeb18Value), $(vSep17COGS), $(vOct17COGS), $(vNov17COGS), $(vDec17COGS), $(vJan18COGS), $(vFeb18COGS)

](delimiter is ',');

NEXT i;

NoConcatenate

ERReadTmp1:

LOAD [Internal Model] as Link_Model,

Num([42948]) as Aug17Qty,

Num([Aug-17 ($)]) as Aug17Value

FROM

Final.xlsx

(ooxml, embedded labels, table is [Aug'17 end result])

where Type='S';

NoConcatenate

ERReadTmp2:

LOAD [Internal Model] as Link_Model,

Num([Aug-17 ($)]) as Aug17COGS

FROM

Final.xlsx

(ooxml, embedded labels, table is [Aug'17 end result])

where Type='COGS';

LET vNoOfRows = NoOfRows('ERReadTmp1');

FOR i = 0 to $(vNoOfRows) - 1

LET vLink_Model = Peek('Link_Model', i, 'ERReadTmp1');

LET vAug17Qty = Peek('Aug17Qty', i, 'ERReadTmp1');

LET vAug17Value = Peek('Aug17Value', i, 'ERReadTmp1');

LET vAug17COGS = Peek('Aug17COGS', i, 'ERReadTmp2');

ERReadTmp:

LOAD * Inline

[

"Link_Model", "Aug17Qty", "Aug17Value", "Aug17COGS"

$(vLink_Model), $(vAug17Qty), $(vAug17Value), $(vAug17COGS)

](delimiter is ',');

NEXT i;

///*

NoConcatenate

SaleQtyValueTable:

LOAD MonthStart(Date#('Apr/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Apr/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty10] as Sale_Quantity,

[Sale Value10] as Sale_Value,

'BP' as [Data Type],

COGS10 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('May/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('May/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty11] as Sale_Quantity,

[Sale Value11] as Sale_Value,

'BP' as [Data Type],

COGS11 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('Jun/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Jun/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty12] as Sale_Quantity,

[Sale Value12] as Sale_Value,

'BP' as [Data Type],

COGS12 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('Jul/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Jul/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty13] as Sale_Quantity,

[Sale Value13] as Sale_Value,

'BP' as [Data Type],

COGS13 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('Aug/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Aug/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty14] as Sale_Quantity,

[Sale Value14] as Sale_Value,

'BP' as [Data Type],

COGS14 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('Sep/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Sep/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty15] as Sale_Quantity,

[Sale Value15] as Sale_Value,

'BP' as [Data Type],

COGS15 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('Oct/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Oct/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty16] as Sale_Quantity,

[Sale Value16] as Sale_Value,

'BP' as [Data Type],

COGS16 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('Nov/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Nov/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty17] as Sale_Quantity,

[Sale Value17] as Sale_Value,

'BP' as [Data Type],

COGS17 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('Dec/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Dec/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty18] as Sale_Quantity,

[Sale Value18] as Sale_Value,

'BP' as [Data Type],

COGS18 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('Jan/01/2018', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Jan/01/2018', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty19] as Sale_Quantity,

[Sale Value19] as Sale_Value,

'BP' as [Data Type],

COGS19 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('Feb/01/2018', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Feb/01/2018', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty20] as Sale_Quantity,

[Sale Value20] as Sale_Value,

'BP' as [Data Type],

COGS20 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('Mar/01/2018', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Mar/01/2018', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty21] as Sale_Quantity,

[Sale Value21] as Sale_Value,

'BP' as [Data Type],

COGS21 as COGS

Resident BPReadTmp;

Concatenate

LOAD MonthStart(Date#('Apr/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Apr/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty22] as Sale_Quantity,

[Sale Value22] as Sale_Value,

'IBP' as [Data Type],

COGS22 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('May/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('May/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty23] as Sale_Quantity,

[Sale Value23] as Sale_Value,

'IBP' as [Data Type],

COGS23 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('Jun/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Jun/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty24] as Sale_Quantity,

[Sale Value24] as Sale_Value,

'IBP' as [Data Type],

COGS24 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('Jul/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Jul/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty25] as Sale_Quantity,

[Sale Value25] as Sale_Value,

'IBP' as [Data Type],

COGS25 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('Aug/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Aug/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty26] as Sale_Quantity,

[Sale Value26] as Sale_Value,

'IBP' as [Data Type],

COGS26 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('Sep/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Sep/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty27] as Sale_Quantity,

[Sale Value27] as Sale_Value,

'IBP' as [Data Type],

COGS27 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('Oct/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Oct/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty28] as Sale_Quantity,

[Sale Value28] as Sale_Value,

'IBP' as [Data Type],

COGS28 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('Nov/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Nov/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty29] as Sale_Quantity,

[Sale Value29] as Sale_Value,

'IBP' as [Data Type],

COGS29 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('Dec/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Dec/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty30] as Sale_Quantity,

[Sale Value30] as Sale_Value,

'IBP' as [Data Type],

COGS30 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('Jan/01/2018', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Jan/01/2018', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty31] as Sale_Quantity,

[Sale Value31] as Sale_Value,

'IBP' as [Data Type],

COGS31 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('Feb/01/2018', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Feb/01/2018', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty32] as Sale_Quantity,

[Sale Value32] as Sale_Value,

'IBP' as [Data Type],

COGS32 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('Mar/01/2018', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Mar/01/2018', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sale Qty33] as Sale_Quantity,

[Sale Value33] as Sale_Value,

'IBP' as [Data Type],

COGS33 as COGS

Resident IBPReadTmp;

Concatenate

LOAD MonthStart(Date#('Sep/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Sep/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Sep17Qty] as Sale_Quantity,

[Sep17Value] as Sale_Value,

'PSI' as [Data Type],

[Sep17COGS] as COGS

Resident PSIReadTmp;

Concatenate

LOAD MonthStart(Date#('Oct/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Oct/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Oct17Qty] as Sale_Quantity,

[Oct17Value] as Sale_Value,

'PSI' as [Data Type],

[Oct17COGS] as COGS

Resident PSIReadTmp;

Concatenate

LOAD MonthStart(Date#('Nov/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Nov/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Nov17Qty] as Sale_Quantity,

[Nov17Value] as Sale_Value,

'PSI' as [Data Type],

[Nov17COGS] as COGS

Resident PSIReadTmp;

Concatenate

LOAD MonthStart(Date#('Dec/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Dec/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Dec17Qty] as Sale_Quantity,

[Dec17Value] as Sale_Value,

'PSI' as [Data Type],

[Dec17COGS] as COGS

Resident PSIReadTmp;

Concatenate

LOAD MonthStart(Date#('Jan/01/2018', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Jan/01/2018', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Jan18Qty] as Sale_Quantity,

[Jan18Value] as Sale_Value,

'PSI' as [Data Type],

[Jan18COGS] as COGS

Resident PSIReadTmp;

Concatenate

LOAD MonthStart(Date#('Feb/01/2018', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Feb/01/2018', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Feb18Qty] as Sale_Quantity,

[Feb18Value] as Sale_Value,

'PSI' as [Data Type],

[Feb18COGS] as COGS

Resident PSIReadTmp;

Concatenate

LOAD MonthStart(Date#('Aug/01/2017', 'MMM/DD/YY')) & Chr(59) & Link_Model as TblJoinkey,

MonthStart(Date#('Aug/01/2017', 'MMM/DD/YY')) as SaleQtyValueDate,

Link_Model,

[Aug17Qty] as Sale_Quantity,

[Aug17Value] as Sale_Value,

'ER' as [Data Type],

[Aug17COGS] as COGS

Resident ERReadTmp;

DROP Table BPReadTmp;

DROP Table IBPReadTmp;

DROP Table PSIReadTmp1;

DROP Table PSIReadTmp2;

DROP Table PSIReadTmp;

DROP Table ERReadTmp1;

DROP Table ERReadTmp2;

DROP Table ERReadTmp;

RENAME Field SaleQtyValueDate to Link_Date;

DateTmp:

LOAD

min([Link_Date]) as min_Link_Date,

max([Link_Date]) as max_Link_Date

RESIDENT SaleQtyValueTable;

LET vMin_Link_Date = peek('min_Link_Date');

LET vMax_Link_Date = peek('max_Link_Date');

Drop Table DateTmp;

LET vStart = $(vMin_Link_Date);

LET vEnd = $(vMax_Link_Date);

LET vStart = floor(YearStart($(vStart)));

LET vEnd = floor(YearEnd($(vEnd)));

LET NumOfDays = $(vEnd) - $(vStart) + 1;

Date_src:

LOAD

$(vStart) + Rowno() - 1 as DateID

AUTOGENERATE $(NumOfDays);

[MasterCalendar]:

LOAD

DateID as Link_Date, // just in case

date(DateID) as CalDate, // it will be in format defined in your SET DateFormat=, or in your system format

day(DateID) as CalDay,

week(DateID) as CalWeek,

month(DateID) as CalMonth, // simple month name; it is dual - numeric and text

dual(month(DateID) & '-' & year(DateID),

year(DateID) & num(month(DateID), '00')) as CalMonthYear, // Month-Year format, dual

year(DateID) as CalYear,

weekday(DateID) as CalWeekday,

'Q' & ceil(month(DateID)/3) as CalQuarter, // in format Q1, Q2, Q3, Q4

year(DateID) & ceil(month(DateID)/3) as CalQtrYear // Qn-Year, dual

// and whatever else you may want here...

RESIDENT Date_src;

Drop Table Date_src;