Grouping Historical Data into Last Week & Keeping Current and Future Weeks' Data as it is

[elintham]

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 

[sunny_talwar]

Why did you only decide to remove 201827 - 201908 and not another date range? I am trying to understand the logic behind this?

[elintham]

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.