topic Re: Date Range Mapping(From Date and To Date Mapping with Invoice Date from Sales Table ) in QlikView

Date Range Mapping(From Date and To Date Mapping with Invoice Date from Sales Table )

Raggs — Mon, 26 Oct 2020 09:46:09 GMT

Hello Team,

I have a Sales Table and Need to calculate some expenses(Like Commission and Rebate) on Sales.

Rules: Need to calculate Expenses based on Date Ranges(Means: Need to calculate percentage on sales In between the From Date and To Date only )

Below given Sample Data.

Matrix Rules table
BrandID	From Date	To Date	Percentage	Sales
100	01-01-2016	30-06-2016	2.1	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)
150	01-09-2016	31-12-2016	5.5	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)
125	01-01-2016	30-06-2016	6.3	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)
321	01-01-2016	31-12-2016	4.75	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)
546	01-01-2016	31-12-2016	2.5	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)
256	01-09-2016	31-12-2016	1.25	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)

From Sales Table
BrandID	Product ID	Invoice Date	Sales
100	a125	04-01-2016	6500
150	s365	25-01-2016	4500
125	x456	12-02-2016	1250
321	d145	06-03-2016	3650
546	z124	25-07-2016	7850
256	v111	07-07-2016	1256
125	x456	11-08-2016	356
321	d145	15-09-2016	1254
546	z124	11-12-2016	650
256	v111	28-11-2016	950
125	x456	04-05-2016	75

Please check and help me on this ASAP.

Thanks in advance.

Re: Date Range Mapping(From Date and To Date Mapping with Invoice Date from Sales Table )

MayilVahanan — Mon, 26 Oct 2020 10:01:49 GMT

Hi @Raggs

You can achieve based on IntervalMatch concept.

Try like below

T1:
LOAD * INLINE [
BrandID, FromDate, ToDate, Percentage
100, 01-01-2016, 30-06-2016, 2.1
150, 01-09-2016, 31-12-2016, 5.5
125, 01-01-2016, 30-06-2016, 6.3
321, 01-01-2016, 31-12-2016, 4.75
546, 01-01-2016, 31-12-2016, 2.5
256, 01-09-2016, 31-12-2016, 1.25
];

T2:
LOAD * INLINE [
BrandID, ProductID, InvoiceDate, Sales
100, a125, 04-01-2016, 6500
150, s365, 25-01-2016, 4500
125, x456, 12-02-2016, 1250
321, d145, 06-03-2016, 3650
546, z124, 25-07-2016, 7850
256, v111, 07-07-2016, 1256
125, x456, 11-08-2016, 356
321, d145, 15-09-2016, 1254
546, z124, 11-12-2016, 650
256, v111, 28-11-2016, 950
125, x456, 04-05-2016, 75
];

IntervalMatch([InvoiceDate],BrandID)
LOAD
[FromDate], [ToDate],BrandID
Resident T1;

Join
LOAD * Resident T2;

Join
LOAD * Resident T1;

DROP Table T1, T2;

Output:

Re: Date Range Mapping(From Date and To Date Mapping with Invoice Date from Sales Table )

Taoufiq_Zarra — Mon, 26 Oct 2020 10:13:19 GMT

@Raggs May be

in Script :

Data: LOAD * INLINE [ BrandID, From Date, To Date, Percentage 100, 01-01-2016, 30-06-2016, 2.1 150, 01-09-2016, 31-12-2016, 5.5 125, 01-01-2016, 30-06-2016, 6.3 321, 01-01-2016, 31-12-2016, 4.75 546, 01-01-2016, 31-12-2016, 2.5 256, 01-09-2016, 31-12-2016, 1.25 ]; join LOAD * INLINE [ BrandID, Product ID, Invoice Date, Sales 100, a125, 04-01-2016, 6500 150, s365, 25-01-2016, 4500 125, x456, 12-02-2016, 1250 321, d145, 06-03-2016, 3650 546, z124, 25-07-2016, 7850 256, v111, 07-07-2016, 1256 125, x456, 11-08-2016, 356 321, d145, 15-09-2016, 1254 546, z124, 11-12-2016, 650 256, v111, 28-11-2016, 950 125, x456, 04-05-2016, 75 ]; Final: noconcatenate load *,if([Invoice Date]>=[From Date] and [Invoice Date]<=[To Date],1,0) as Flag resident Data; drop table Data;

and in chart

dimension : BrandID, From Date, To Date, Percentage

Measure : =sum({<Flag={1}>}Sales)

output:

Re: Date Range Mapping(From Date and To Date Mapping with Invoice Date from Sales Table )

Raggs — Mon, 26 Oct 2020 11:02:36 GMT

Hello @MayilVahanan

Thanks for your suggestion.

Below is the Sales data from Sales table:

BP Category 1 ID	Business Partner ID	CustGroup	Brand ID	Line ID	Item Family ID	Item Number
653000	653000-001	101	001	145	8	66020-000

Below is the Excel sheet Rules to calculate the Expense(Rebate):

Customer#	Customer Cat	Cust Group	Brand ID	Line ID	Family ID	Item #	From date	To date	Percentage
653000-001							01-01-2016	31-3-2016	7.00

Need to Get the Filed(Customer Cat, Cust Group, Brand ID, Line ID, Family ID, Item# ) data information from the Sales table and Then, Need to calculate the Rebate of 7% on Customer# Sales between the Date range From Date and To Date

Is it possible with Interval Match, If Yes Can you please Help me with Interval match function.

Thanks in Advance.

Re: Date Range Mapping(From Date and To Date Mapping with Invoice Date from Sales Table )

Kushal_Chawda — Mon, 26 Oct 2020 11:16:27 GMT

@Raggs How did you arrive to 7% calculation?

Re: Date Range Mapping(From Date and To Date Mapping with Invoice Date from Sales Table )

MayilVahanan — Mon, 26 Oct 2020 11:17:46 GMT

Create the composite key in both tables and use that composite key instead of "BrandID" in the above examples.

Hope it helps

Re: Date Range Mapping(From Date and To Date Mapping with Invoice Date from Sales Table )

Raggs — Mon, 26 Oct 2020 11:18:54 GMT

@Kushal_Chawda

7% has given by Business user in Excel sheet

Re: Date Range Mapping(From Date and To Date Mapping with Invoice Date from Sales Table )

Raggs — Mon, 26 Oct 2020 12:03:38 GMT

Hello

I have created composite key, however for the customer# we are not getting the remaining filed values

Re: Date Range Mapping(From Date and To Date Mapping with Invoice Date from Sales Table )

Kushal_Chawda — Mon, 26 Oct 2020 12:27:09 GMT

@Raggs try below.

Make sure that the sequence of fields in composite key is same at both the place.

Map_Percent: mapping load Customer#&'|'&[Customer Cat]&'|'&[Cust Group]&'|'&[Brand ID]&'|'&[Line ID]&'|'&[Family ID]&'|'&[Item #]&'|'&floor(Date) as Key, Percentag; load Customer#, [Customer Cat], [Cust Group], [Brand ID], [Line ID], [Family ID], [Item #], [From date]+ iterno()-1 as Date , [To date], Percentag FROM Excel while [From date]+ iterno()-1<=[To date]; Sales: LOAD [BP Category 1 ID], applymap('Map_Percent', [Business Partner ID]&'|'&[BP Category 1 ID]&'|'&[Business Partner ID]&'|'&CustGroup&'|'&[Brand ID]&'|'&[Line ID]&'|'&[Item Family ID]&'|'&[Item Number]&'|'&floor([Invoice Date])) as Percent, [Product ID] CustGroup, [Brand ID], [Line ID], [Item Family ID], [Item Number], [Product ID], [Invoice Date] FROM Sales;