
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Only get the closest date
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 | Price |
---|---|---|---|
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try with Rank() on Date field

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Rank is a chart fonction not a script function right?
You mean i have to use it in my table result on date fields?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
