Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
See Attachment
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
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
maybe like attached?
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
see attached.
hope this is helpful
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
PFA
Regards
Pradosh
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
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