topic Re: How would you Cross Table & Load Script this table? in QlikView

How would you Cross Table & Load Script this table?

nickjose7 — Wed, 25 Nov 2020 16:16:04 GMT

‌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

Re: How would you Cross Table & Load Script this table?

Frank_Hartmann — Sat, 07 Oct 2017 10:48:11 GMT

see attached file

Skript:

CrossTable(Date, Data, 2)

LOAD *

FROM

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

NoConcatenate

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

Re: How would you Cross Table & Load Script this table?

nickjose7 — Sat, 07 Oct 2017 13:21:46 GMT

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

Re: How would you Cross Table & Load Script this table?

Frank_Hartmann — Sat, 07 Oct 2017 15:30:41 GMT

maybe like attached?

Re: How would you Cross Table & Load Script this table?

nickjose7 — Sat, 07 Oct 2017 16:28:01 GMT

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

Re: How would you Cross Table & Load Script this table?

Frank_Hartmann — Sat, 07 Oct 2017 17:13:57 GMT

see attached.

hope this is helpful

Re: How would you Cross Table & Load Script this table?

pradosh_thakur — Sat, 07 Oct 2017 18:45:30 GMT

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

Re: How would you Cross Table & Load Script this table?

pradosh_thakur — Sat, 07 Oct 2017 19:20:54 GMT

PFA

Regards

Pradosh

Re: How would you Cross Table & Load Script this table?

antoniotiman — Sat, 07 Oct 2017 20:31:42 GMT

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

Re: How would you Cross Table & Load Script this table?

nickjose7 — Sun, 08 Oct 2017 02:04:10 GMT

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

Re: How would you Cross Table & Load Script this table?

nickjose7 — Sun, 08 Oct 2017 02:05:20 GMT

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

Nick

Re: How would you Cross Table & Load Script this table?

nickjose7 — Sun, 08 Oct 2017 02:06:18 GMT

This is great. Only one step behind.

Thanks for the help!!

Nick

Re: How would you Cross Table & Load Script this table?

nickjose7 — Sun, 08 Oct 2017 02:16:51 GMT

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

Re: How would you Cross Table & Load Script this table?

nickjose7 — Sun, 08 Oct 2017 03:49:55 GMT

Hi Pradosh,

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

Re: How would you Cross Table & Load Script this table?

pradosh_thakur — Sun, 08 Oct 2017 04:04:45 GMT

working on that.

do you not need Types other than Alpha and Q?

Re: How would you Cross Table & Load Script this table?

nickjose7 — Sun, 08 Oct 2017 04:25:11 GMT

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:

Re: How would you Cross Table & Load Script this table?

nickjose7 — Sun, 08 Oct 2017 04:26:11 GMT

final output required:

Re: How would you Cross Table & Load Script this table?

pradosh_thakur — Sun, 08 Oct 2017 04:26:38 GMT

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

Re: How would you Cross Table & Load Script this table?

pradosh_thakur — Sun, 08 Oct 2017 04:35:37 GMT

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

Re: How would you Cross Table & Load Script this table?

nickjose7 — Sun, 08 Oct 2017 04:39:37 GMT

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