Re: rates by week - Qlik Community

ecabanas · ‎2020-05-15

Hi all,

At the end of the loading process of different tables, at the end I have this order, where I have the items and the rates with their time range: From and To

ItemId	Proveedor_Tarifa	Tarifa	From_Date	Row	Tarifa_Unit	To_
10356751	PROV00026	5,85	01/01/2011	1	750	31/12/2011
10356751	PROV00026	5,85	01/01/2012	2	750	31/12/2012
10356751	PROV00026	5,85	01/01/2013	3	750	31/12/2013
10356751	PROV00026	5,85	01/01/2014	4	750	31/12/2014
10356751	PROV00026	5,85	01/01/2015	5	750	31/12/2015
10356751	PROV00026	5,09	01/01/2016	6	750	31/12/2020
21129861	PROV00026	2,17	01/01/2012	7	200	31/12/2012
21129861	PROV00026	1,94	01/01/2013	8	200	31/12/2013
21129861	PROV00026	1,96	01/01/2014	9	200	31/12/2014
21129861	PROV00026	1,96	01/01/2015	10	200	31/12/2020

What I would like to do, and I have no way to do it, is to separate these rates, create the corresponding rate for each week of the year, creating a new row for each week and rate. Because in the real model, the rates change every week

Can you think of a way to do it?

Thanks a lot.

Eduard

marcel_olmo · ‎2020-05-16

Hello @ecabanas I think it would be easier for us to understand your problem if you post your example with your desirable solution made by a dummy example. And then, we can figure it out how to do it with Qlik Code.

Hope that helps.

Best regards, Marcel.

ecabanas · ‎2020-05-16

Hi Marcel

Yes sorry, I explain my problem terrible bad

I have a table like this:

ItemId, SalesDate

1, 01/04/2011

1, 02/05/2011

1, 01/05/2011

1, 17/05/2011

And I have another with the rates:

ItemId, From_Date,To_Date,Rate

1, 01/04/2011,15/04/2011, 1

1, 15/04/2011,30/04/2011, 1

1, 01/05/2011,31/05/11, 1.5

Since now I create a rate for each month, generating a key with monthend(date), now the rates change every week or several days, and this approach did not work.

How could I say to Sales table to choose the correct rate depending on the rate period?

Thank's for your help

marcel_olmo · ‎2020-05-16

I see, you have a concept you want to measure between dates. In that case, you need to use the intervalmatch function. Here I attach an example with a table of rentals like this :

and in my example you have a calendar of a masterCalendar that links between the minimum date of the start date, and the maximum date of endate.

Hope that helps.

Best Regards, Marcel.

ecabanas · ‎2020-05-17

Hi @marcel_olmo

Thank's for your example file, but I could not understand in my case how could I create a table with the sales and the correspondent rate. Sorry but I could not know how to apply my example with your

Thank's again

Eduard

ecabanas · ‎2020-05-17

Hi @marcel_olmo

Th eproblem comes when I have two items. Your key is orderdate, but I could not do it per order date, for example my sales table has:

Orderid, itemid, salesdate

and rates

itemid, from_date,to_date, rate

I tried to do your approach with my data but did not work, any idea how to do it with "N" Items?

Sales:
Load ItemId, Date inline [
ItemId,Date
21129481,09/08/2019
21129481,21/11/2019
21129481,27/02/2020
21129481,16/04/2020
21129481,14/05/2020
];

Rates:
Load ItemId,From_D,To_D,Tarifa  inline [
ItemId,From_D,To_D,Tarifa
21129481,09/08/2019,14/08/2019,1,47
21129481,15/08/2019,21/08/2019,1,47
21129481,22/08/2019,28/08/2019,1,47
21129481,29/08/2019,04/09/2019,1,47
21129481,05/09/2019,11/09/2019,1,47
21129481,12/09/2019,18/09/2019,1,47
21129481,19/09/2019,25/09/2019,1,47
21129481,26/09/2019,02/10/2019,1,47
21129481,03/10/2019,09/10/2019,1,47
21129481,10/10/2019,16/10/2019,1,47
21129481,17/10/2019,23/10/2019,1,47
21129481,24/10/2019,30/10/2019,1,47
21129481,31/10/2019,06/11/2019,1,47
21129481,07/11/2019,13/11/2019,1,47
21129481,14/11/2019,20/11/2019,1,47
21129481,21/11/2019,27/11/2019,1,47
21129481,28/11/2019,04/12/2019,1,47
21129481,05/12/2019,11/12/2019,1,23
21129481,12/12/2019,18/12/2019,1,23
21129481,19/12/2019,24/12/2019,1,23
21129481,25/12/2019,27/12/2019,1,23
21129481,28/12/2019,01/01/2020,1,23
21129481,02/01/2020,05/01/2020,1,23
21129481,06/01/2020,08/01/2020,1,23
21129481,09/01/2020,15/01/2020,1,23
21129481,16/01/2020,22/01/2020,1,23
21129481,23/01/2020,29/01/2020,1,23
21129481,30/01/2020,05/02/2020,1,23
21129481,06/02/2020,12/02/2020,1,23
21129481,13/02/2020,19/02/2020,1,23
21129481,20/02/2020,26/02/2020,1,23
21129481,27/02/2020,04/03/2020,1,23
21129481,05/03/2020,11/03/2020,1,23
21129481,12/03/2020,18/03/2020,1,23
21129481,19/03/2020,25/03/2020,1,23
21129481,26/03/2020,01/04/2020,1,23
21129481,02/04/2020,08/04/2020,1,23
21129481,09/04/2020,15/04/2020,1,23
21129481,16/04/2020,22/04/2020,1,23
21129481,23/04/2020,29/04/2020,1,23
21129481,30/04/2020,06/05/2020,1,23
21129481,07/05/2020,13/05/2020,1,23
21129481,14/05/2020,31/12/2020,1,23
];
IntervalSingDates:
LOAD *,
	"From_D" & ' - ' & "To_D"& ' - ' &	"ItemId"								as	"LinkFecha";
LOAD
    "ItemId",// as ItemId_,
    "From_D",
    "To_D"
Resident Rates;
drop Table Rates;
CalendarTemp:
Load 
 Min("From_D")			AS MinDate,
   Max("To_D")			AS MaxDate
   
Resident IntervalSingDates;

LET vdatemin= num(peek('MinDate',0,'CalendarTemp'));
LET vdatemax= num(peek('MaxDate',0,'CalendarTemp'));

Drop table CalendarTemp;

// Calendar:
// load
// 	//date($(vdatemin) + RowNo()-1) as num,
// 	date($(vdatemin) + RowNo()-1) as OrderDate
// 	AutoGenerate
// 	$(vdatemax) - $(vdatemin) + 1;

Bridge:
IntervalMatch("From_D")
LOAD 
//"ItemId",
//"From_D"		AS Date,
     "To_D"		AS EndDate
	resident IntervalSingDates;
	
BridgeTotal:
NoConcatenate
LOAD From_D as Date,
	 EndDate as EndDate_,
     //OrderDate,
     From_D & ' - ' & EndDate										as	"LinkFecha"
RESIDENT Bridge;

DROP TABLE Bridge;

thank you very much

marcel_olmo · ‎2020-05-19

Ok @ecabanas I understand the problem now.

You have to do the same intervalmatch only by dates and then add the dimensions yo'd like after.

I've updated your example with the solution as you can see in the image :

marcel_olmo · ‎2020-05-26

Hi @ecabanas the solution was fine for you?

Regards, Marcel.