Data Modelling Challenge

nickjose7 · ‎2017-10-10

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'

vvira1316 · ‎2017-10-15

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;

Related Topics