Re: Data Modelling Challenge - Qlik Community

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'

MK9885 · ‎2017-10-10

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.

vvira1316 · ‎2017-10-10

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;

nickjose7 · ‎2017-10-11

Hi Vijay,

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

Many Thanks

Nick

vvira1316 · ‎2017-10-11

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

vvira1316 · ‎2017-10-11

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

vvira1316 · ‎2017-10-12

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

nickjose7 · ‎2017-10-12

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

vvira1316 · ‎2017-10-12

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

nickjose7 · ‎2017-10-13

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