topic Re: rates by week in App Development

rates by week

ecabanas — Fri, 15 May 2020 07:08:37 GMT

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

Re: rates by week

marcel_olmo — Sat, 16 May 2020 10:24:36 GMT

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.

Re: rates by week

ecabanas — Sat, 16 May 2020 12:00:55 GMT

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

Re: rates by week

marcel_olmo — Sat, 16 May 2020 17:40:02 GMT

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.

Re: rates by week

ecabanas — Sun, 17 May 2020 06:09:26 GMT

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

Re: rates by week

ecabanas — Sun, 17 May 2020 09:01:36 GMT

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

Re: rates by week

marcel_olmo — Tue, 19 May 2020 10:27:49 GMT

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 :

Re: rates by week

marcel_olmo — Tue, 26 May 2020 10:17:22 GMT

Hi @ecabanas the solution was fine for you?

Regards, Marcel.