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
You are right. I'm sorry for mentioning the wrong value. Thanks for pointing out.
Nick
This is great. Only one step behind.
Thanks for the help!!
Nick
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
Hi Pradosh,
Can you do this on backend and create a field for (b/c)?
working on that.
do you not need Types other than Alpha and Q?
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:
final output required:
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
Check it out i have updated the app and put a screenshot too. let me know if that helps
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