Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik Community,
I have two types of Backlog Quantities that I would like to compare but they both have different Fiscal Weeks. I have mapped the two dates together in the data load script, but am facing an issue when I'm trying to group the historical weeks' data into one particular week while keeping the rest of the weeks as they are.
For example, I have the below two tables looking at Mkt Part Num, Customer and Ship To Country by Week (ie 201827 - Year 2018, Week 27). I've mapped the two weeks together to obtain the Final Output, but I would like to combine the historical weeks data together into one particular week for example, to combine all the data that is prior to Week 201908 into 201908 while the rest of the weeks remain. Does anyone have any idea how to solve this in the script? I would also need to do this without affecting other reports that are using the same script. I was thinking maybe create a filter that I could use in the expression instead?
#1: Backlog Type A
Mkt Part Num | Customer | Ship to Country | 201827 | 201845 | 201908 | 201909 | 201910 | 201911 | 201912 | 201913 |
ABC | Adam | GERMANY | 2400 | 3000 | 2400 | 1800 | 2400 | |||
ABC | Alan | BRAZIL | 960 | 720 | 360 | |||||
ABC | Belle | INDIA | 1200 | 1200 | ||||||
ABC | Mary | MALAYSIA | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 |
#2: Backlog Type B
Mkt Part Num | Customer | Ship to Country | 201827 | 201845 | 201907 | 201909 | 201910 | 201911 | 201912 | 201914 |
ABC | Adam | GERMANY | 1800 | 3000 | 2400 | 3000 | 1800 | |||
ABC | Alan | BRAZIL | 480 | 480 | 720 | |||||
ABC | Belle | INDIA | 1200 | 1200 | 2400 | |||||
ABC | Mary | MALAYSIA | 1200 | 1200 | 1200 | 1200 |
Current Final Output:
Mkt Part Num | Customer | Ship to Country | Backlog Type | 201827 | 201845 | 201907 | 201908 | 201909 | 201910 | 201911 | 201912 | 201913 | 201914 |
ABC | Adam | GERMANY | A | 2400 | 3000 | 2400 | 1800 | 2400 | |||||
ABC | Adam | GERMANY | B | 1800 | 3000 | 2400 | 3000 | 1800 | |||||
ABC | Adam | GERMANY | Delta | 1800 | 4800 | 4800 | 4800 | 2400 | 1800 | 2400 | 2400 | 0 | 0 |
ABC | Alan | BRAZIL | A | 960 | 720 | 360 | |||||||
ABC | Alan | BRAZIL | B | 480 | 480 | 720 | |||||||
ABC | Alan | BRAZIL | Delta | 0 | 480 | 480 | 480 | -480 | -720 | -360 | -360 | -360 | -360 |
ABC | Belle | INDIA | A | 1200 | 1200 | ||||||||
ABC | Belle | INDIA | B | 1200 | 1200 | 2400 | |||||||
ABC | Belle | INDIA | Delta | 0 | 0 | 2400 | 2400 | 2400 | 2400 | 2400 | 2400 | 2400 | 2400 |
ABC | Mary | MALAYSIA | A | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | ||||
ABC | Mary | MALAYSIA | B | 1200 | 1200 | 1200 | 1200 | ||||||
ABC | Mary | MALAYSIA | Delta | 0 | 0 | 0 | -1200 | -1200 | -2400 | -2400 | -2400 | -3600 | -2400 |
Desired Final Output:
Here you will see that the data from weeks 201827 - 201908 above have been combined into 201908 while the rest of the weeks' data remain unchanged.
Mkt Part Num | Customer | Ship to Country | Backlog Type | 201908 | 201909 | 201910 | 201911 | 201912 | 201913 | 201914 |
ABC | Adam | GERMANY | A | 2400 | 3000 | 2400 | 1800 | 2400 | ||
ABC | Adam | GERMANY | B | 4800 | 2400 | 3000 | 1800 | |||
ABC | Adam | GERMANY | Delta | 4800 | 2400 | 1800 | 2400 | 2400 | 0 | 0 |
ABC | Alan | BRAZIL | A | 960 | 720 | 360 | ||||
ABC | Alan | BRAZIL | B | 480 | 480 | 720 | ||||
ABC | Alan | BRAZIL | Delta | 480 | -480 | -720 | -360 | -360 | -360 | -360 |
ABC | Belle | INDIA | A | 2400 | ||||||
ABC | Belle | INDIA | B | 4800 | ||||||
ABC | Belle | INDIA | Delta | 2400 | 2400 | 2400 | 2400 | 2400 | 2400 | 2400 |
ABC | Mary | MALAYSIA | A | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | |
ABC | Mary | MALAYSIA | B | 1200 | 1200 | 1200 | 1200 | |||
ABC | Mary | MALAYSIA | Delta | -1200 | -1200 | -2400 | -2400 | -2400 | -3600 | -2400 |
Below is my current script:
Backlog_All:
Load
*,
FROM [Lib:xxx] (qvd);
CRS:
Mapping Load
OrderID,
CRS_WEEK
Resident Backlog_All;
RS:
Mapping Load
OrderID,
RS_WEEK
Resident Backlog_All;
Final_Backlog_All:
Load
*,
ApplyMap('CRS',OrderID, 'OTHER') as FiscalWeek,
'CRS' as BacklogType,
1 as Status
Resident Backlog_All;
Load
*,
ApplyMap('RS',OrderID, 'OTHER') as FiscalWeek,
'RS' as BacklogType,
0 as Status
Resident Backlog_All;
Drop table Backlog_All;
Thanks,
Elin
Hi @sunny_talwar would you be able to help with this? thanks
Why did you only decide to remove 201827 - 201908 and not another date range? I am trying to understand the logic behind this?
Hi Sunny,
The data under 201827 - 201908 wasn't removed but rather combined under 201908. So what I did manually to achieve the Final Desired Output was to sum the Types A & B Backlog for all past weeks under 201908. For example: ABC/Adam/Germany/Type B - I summed 1800 & 3000 under weeks 201827 & 201845 and put that under 201908.
Ideally we just want to combine the data into a particular week for example last week so that we don't need to show so many weeks in the data. Hope that clarifies your question.