10 Replies Latest reply: Oct 15, 2017 8:08 PM by Vijay Vira RSS

    Data Modelling Challenge

    Nick Jose

      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
          Aehman K

          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
            Vijay Vira

            Hi Nick,

             

             

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

             

            DMChallenge.PNG

             

            DMChallenge2.PNG

             

             

            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
                Nick Jose

                Hi Vijay,

                 

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

                 

                Many Thanks

                Nick

                  • Re: Data Modelling Challenge
                    Vijay Vira

                    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
                      Vijay Vira

                      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

                       

                      TblJoinKeyValuesToBeDropped.PNG

                       

                      DMChallenge3.PNG

                       

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

                  • Re: Data Modelling Challenge
                    Vijay Vira

                    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
                        Nick Jose

                        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
                            Vijay Vira

                            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
                                Nick Jose

                                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
                                    Vijay Vira

                                    Hi Nick,

                                     

                                    How about following....

                                     

                                    DM_Challenge.JPG

                                     

                                     

                                     

                                    DM_Challenge2.JPG

                                     

                                     

                                    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;