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

[Frank_Hartmann]

see attached file

Skript:

1:

CrossTable(Date, Data, 2)

LOAD *

FROM

(ooxml, embedded labels, table is [Aug'17 end result]);

NoConcatenate

2:

Load *,num(Data) as Data1,

if(wildmatch(Date,'*-*'),Date,Date(num#(Date),'MMM YY')) as Date1

Resident 1; DROP table 1;

Straight Table:

Dim:          Model, Date1, Type

Exp1:        sum({<Type={'ALPHA'}>}Data)

Exp2:        sum({<Type={'Q'}>}Data)

hope this helps

[nickjose7]

Dear Frank,

Thanks for the quick help. Could you please revise your solution to match the below shown output:

I have revised the source data to bring values against the second model. (Attached with the original post)

Please note that:

1. I need the Link_Date for linking with the Master Calender.

2. The expressions remain same as:

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

(c) Calculate Model wise Sum of [Aug-17] for 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.

Looking forward to your solution.

Best

Nick

[Frank_Hartmann]

maybe like attached?

[nickjose7]

thanks Frank! Looks like we are close but the values are still not matching.

I changed your expression from sum({<Type={'R'}>}Data) to sum({<Type={'Q'}>}Data) which was the requirement but it gives Aug-17 value for Model ABC-1 as 12130 which is the sum of Aug-17 value + Aug-17 ($) value for this model.

The correct output has to be 841. Infact all the output has to be exactly as below:

Please help!

Nick

[Frank_Hartmann]

see attached.

hope this is helpful

[pradosh_thakur]

hi

I have been trying to replicate your results but 1470 seems to be the output for September not august.else everything seems fine.

Is it by mistake or it has to be 1470 for AUG-17 and ALPHA?

regards

Pradosh

[pradosh_thakur]

PFA

Regards

Pradosh

[antoniotiman]

Hi Nick

Temp:
CrossTable(Date,Value,2)
LOAD Model,
Type,
[42948],
[Aug-17 ($)],
[42979],
[Sep-17 ($)],
[43009],
[Oct-17 ($)],
[43040],
[Nov-17 ($)]
FROM
https://community.qlik.com/servlet/JiveServlet/download/1357932-298642/Test.xlsx
(ooxml, embedded labels, table is [Aug'17 end result]);
Temp1:
NoConcatenate
LOAD Model,Type,Dual(Alt(Date(Num#(Date),'MMM-YY'),Date),Date) as Month,
Alt(Date(Num#(Date)),Date(Date#(Left(Date,6),'MMM-YY'))) as Link_Date,
If(IsNum(Num#(Date)),If(Type='Q',Value),If(Type='ALPHA',Value)) as Value
Resident Temp;

Regards,

Antonio

[nickjose7]

Thanks Frank.

I'm sorry for mentioning a wrong value '1470' in the desired output. It had to be 7051. Reattaching the solution:

Also need to calculate these at the backend and create a field which gives column 4/column 3 i.e. 8.38 (7051/841) for ABC-1.

Really appreciate your help!

Nick