Only get the closest date - Qlik Community

Anonymous · ‎2018-01-09

Hi everyone,

I have 2 tables:

LOAD
Sales_ID,

Agency,
Article_ID,
Agency&'_'&Article_ID as Key_Sales ,
Quantity,

Sales_Price,

Sales_Date,

Delivery_Date

from SALES.QVD

LOAD

Agency,

Article_ID,
Agency&'_'&Article_ID as Key_Sales ,
Price_modification_date,

Cost u price

from SALESPRICE.QVD

I link these tables with the key : Agency&'_'&Article_ID as Key_Sales
So i have a duplication for each modification price made

We sometimes modify our sales price but we can't directly know for a Sales_ID which price i have to apply.
I have to only catch the lower closest date

Example: I only need the red bold row on the following tab

Sales_ID	Article	Sales date	Quantity	Cost u price	price modif date	sales_price	total
00001	STABILO	11/10/2017	12	2,52	01/01/2013	4.99	27.24
00001	STABILO	11/10/2017	12	2,68	01/01/2014	4.99	27.24
00001	STABILO	11/10/2017	12	2,49	01/01/2016	4.99	27.24
00001	STABILO	11/10/2017	12	2,72	01/01/2017	4.99	27.24
00001	STABILO	11/10/2017	12	2,44	01/01/2018	4.99	27.24

Total is : (Sales_price-Cost u price) x Quantity

01/01/2017 is the lower closest date based on the sale date. (It was the good price but it's not the current one)

How can I only catch for each SalesID the unique row associated based on this rule?

Thanks to everyone,
Floren

marcus_sommer · ‎2018-01-10

I would use the following approach of joining the SalesPrice table to the Sales table or creating a mapping-table for the merging of the tables. But this needs some further steps which are:

- extending the SalesPrice to a from- and a to-date which could be done per Peek() or Previous() ? within a sorted resident load of the data

- then using an intervalmatch to get for each day between from and to a record with a date: IntervalMatch

- combining Agency&'_'&Article_ID&'_'&NewDateField as Key_Sales

- using the extended key for the joining or mapping

and you will get the correct price for each sales-record.

- Marcus

Anonymous · ‎2018-01-11

Thanks marcus for your answer,

I tried to get from date to date, but previous() gave me the opposite result.

Here is my script:

TMPSales:

Load

*,

Agency&'_'&Article_ID as Key_Sales,

MakeDate([YearModifPrice],[MonthModifPrice],[DayModifPrice]) as [Start price modif date]

FROM SALESPRICE.QVD

SalesPrice:

LOAD

Key_Sales,

[Price modif date],

IF(Key_Sales=Previous(Key_Sales), Previous([Start price modif date]), [Start price modif date]) as [End price modif date]

Resident TMPSales;

Result:

What I expect is the opposite:

Key_sales	Start price modif date	End price modif date
300-338040	23/01/2016	01/02/2016
300-338040	01/02/2016	01/02/2017
300-338040	01/02/2017	01/02/2017
300-338040	18/09/2017	01/02/2018
300-338040	01/02/2018	-

I tried with Resident TMPSales order by [Start price modif date] desc; but it didnt works...

Result:

Did i miss something?

Thanks by advance

zebhashmi · ‎2018-01-11

try with Rank() on Date field

Anonymous · ‎2018-01-11

Rank is a chart fonction not a script function right?
You mean i have to use it in my table result on date fields?

Anonymous · ‎2018-01-11

I finally get what i needed using peek function.
So now i have from date and to date.

I have this structure now:

SalesPrice:

Key_Sales,

[Start price modif date],

[End price modif date],

[Price]

Sales:

Key_Sales,

[Sales date],

[Qty]

I tried to used matchInterval to link my SalesPrices table with my Sales table but how can I integrate this using my Key_Sales + matchInterval with [sales date] as you mentionned in your message Marcus?

marcus_sommer · ‎2018-01-11

I must admit that I personally nearly never use the intervalmatch else I use in the most cases a while-loop because it's more flexible in some ways and I seldom want to keep the intervalmatch-table else I usually want to merge the data to my other tables. Nevertheless I suggest the above mentioned link because HIC explained the logic behind the methods very well.

Therefore I would probably do the following:

SalesPrice:

load

Key_Sales,

date([Start price modif date] + iterno() - 1) as [Sales date],

[Price]

resident SalesPriceTemp while [Start price modif date] + iterno() - 1 <= [End price modif date];

Sales:

load

Key_Sales,

[Sales date],

[Qty]

from Sales;

left join(Sales)

load * resident SalesPrice;

Instead the join you could also use a mapping whereby you would need to extend the Key_Sales with [Sales date].

- Marcus

Anonymous · ‎2018-01-16

Hi!

It seems to work, I have to validate with the business! Thanks.

But I have 2.000.000 rows into sales price and it takes 2 hours to do this step:

SalesPrice:

load

Key_Sales,

date([Start price modif date] + iterno() - 1) as [Sales date],

[Price]

resident SalesPriceTemp while [Start price modif date] + iterno() - 1 <= [End price modif date];

Do you have an idea to improve this part?

Thanks again,

Florentcot

marcus_sommer · ‎2018-01-16

This could be speed up with an incremental load-approach - maybe with something like this:

SalesPrice:

Key_Sales, [Start price modif date], [End price modif date], Price]

from SalesPrice.qvd (qvd);

concatenate(SalesPrice)

load

Key_Sales,

date([Start price modif date] + iterno() - 1) as [Sales date],

[Price]

while [Start price modif date] + iterno() - 1 <= [End price modif date];

load Key_Sales, [Start price modif date], [End price modif date], [Price]

from SalesPrice where not exists(Key_Sales):

There are in general different ways how it could be applied but the main-idea is to transform only the new and/or changed record and loading the earlier ones from a qvd. Within the last two link-blocks here: Advanced topics for creating a qlik datamodel you will find various explanations and examples how incremental load-approaches could be implemented and how to keep the qvd-loadings optimized.

- Marcus