How would you Cross Table & Load Script this table?

[nickjose7]

‌its.anandrjs antoniotiman‌ serj_shu‌ kush141087‌ @franky_h79

Hi Guys,

Looking for your help once again:

I want to load this table (excel attached) in QlikView:

Model	Type	Aug-17	Aug-17 ($)	Sep-17	Sep-17 ($)	Oct-17	Oct-17 ($)	Nov-17	Nov-17 ($)
ABC-1	P	5	82	0	0	0	0	0	0
ABC-1	Q	841	11,289	150	2,637	0	0	0	0
ABC-1	R	219	2,146	69	676	69	676	69	676
ABC-1	Delta(%)	37.54	44.25	0	0
ABC-1	ALPHA	7,051	1,470	0	0
DPX-23	P	0	0	0	0	0	0	0	0
DPX-23	Q	340	8000	0	0	0	0	0	0
DPX-23	R	36	427	36	427	36	427	36	427
DPX-23	Delta(%)	0	0	0	0
DPX-23	ALPHA	0	5000	0	0

Where:

Column [Aug-17] gives Sales Quantity and Column [Aug-17 ($)] gives Sales Value.  Both Columns have 5 types of values viz. P, Q, R, Delta(%) and ALPHA for a single Model.

Only third and fourth columns are to be considered as every month this table will be replaced with a new one where the latest data be supplied in column 3rd and 4th. e.g if currently we are considering [Aug-17] and [Aug-17 ($)], by next month these two columns will be replaced by [Sep-17] and [Sep-17 ($)]

Now I want to achieve the following:

(a) Make a Link Date from the third or fourth column which are [Aug-17] and [Aug-17 ($)] respectively. (Actual value of [Aug-17] on loading in QlikView shows 42948)

Another option: the sheet name of this excel also gives month name as: [Aug'17 end result]

(b) Calculate Model wise Sum of [Aug-17 ($)] where Type=ALPHA.

(c) Calculate Model wise Sum of [Aug-17] where Type=Q

(d) Calculate (b) / (c)

(e) Write a Load script by which each month when this file is replaced at source, the current data is kept in the data model and the latest gets appended. Not sure but may be I am looking for an Incremental Load Script.

Data Source is MS Excel as attached.

Desired Output:

Sample data revised to add values against all models. Desired Output Screenshot added. Message was edited by: Nick Jose

[pradosh_thakur]

what will be the maximum number of column you will have ? if you have maximum of 10 column it will not fail you. you can try by changing it to September. if you will have more than 10 columns than it may fail. let me get back to you with the column selection part.

Pradosh

[antoniotiman · Accepted solution]

See Attachment

[nickjose7]

Thanks Antonio. This is great. Just that we need to restrict link date to only 01/08/2017 meaning only Columns 'C' and 'D' are to be considered from the excel. Next month the values will be changed and the Link_Date should be 01/09/2017.

thanks for your help. I will be using part of your script as well as Pradosh's as shared above.

best

Nick

[nickjose7]

Can you share your email?

[antoniotiman]

It is enough to add to Script :

Inner Join LOAD DISTINCT Min(Link_Date) as Link_Date
Resident Temp1
Group By Model;

[nickjose7]

Thanks Antonio. This is working however when I replace columns [Aug-17] & [Aug-17 ($)] with [Sep-17] & [Sep-17 ($)]

at the source, the values of 'Value1' are lost.

Any idea why is this happening?

[antoniotiman]

Replace

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;Aug;set;ott;nov;dic';

with Your MonthNames

[nickjose7]

This worked. Many Thanks.

[nickjose7]

Hi Antonio,

I have used your solution and need help in obtaining the below:

1. [Aug'17 ($)] Value / [Aug-17] Value

     where Type='S' as Value3 (If required Group By Model)

2. Now, have to exclude those transactions where Value3<'500'.

This has to be done before calculating [ALPHA per unit] which you had earlier shared.

Looking forward to hear from you.