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

[nickjose7]

You are right. I'm sorry for mentioning the wrong value. Thanks for pointing out.

Nick

[nickjose7]

This is great. Only one step behind.

Thanks for the help!!

Nick

[nickjose7]

Perfect Solution Antonio, like always. Thanks a lot.

Now the only thing is, I need to do this in the backend and create a field say 'Alpha Per Unit' which will give me values 8.38 and 14.7 against the models.

Purpose: this will be taken to Data Model.

Seeking your quick response.

Nick

[nickjose7]

Hi Pradosh,

Can you do this on backend and create a field for (b/c)?

[pradosh_thakur]

working on that.

do you not need Types other than Alpha and Q?

[nickjose7]

No, other types are not required. Even the 'Type' filter is not required. Only the field with values of b/c is required against models.

The final output should be this:

[nickjose7]

final output required:

[pradosh_thakur]

done .

PFA. straight table_new is the table i am using.Everything shifted to script level.Still check once by changing the data and let me know if it is working.

edit : Updated  the app as you needed that list box too. check it out.

regards

Pradosh

[pradosh_thakur]

Check it out i have updated the app and put a screenshot too. let me know if that helps

[nickjose7]

Excellent Solution Pradosh. Thank you so much for your help!!

Just one thing to check: How can we ensure that only the columns 'C' and 'D' are picked every month from the source meaning this month it is [Aug-17] and [Aug-17 ($)]; Next month there will be a new file and the Column 'C' and 'D' will have [Sep-17] and [Sep-17 ($)]. Rest columns are to be ignored.

I will be marking this as the correct answer!!

Thanks

Nick