Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

luizcdepaula
Contributor II

Condition based on date range

Hello experts,

I have a challenge that I thought it was going to be easy, but I definitely was not able to come up with the solution.

There was a price change for a material where the initial price was $16.8 per case with price valid from 1/1/2011 and good until 3/31/2018. With the price change, starting on 4/1/2018, each case will now cost $20. So, I need to make a date comparison to the price date range.

Example:

If "Forecast Month" > "Valid from" and "Forecast Month" < "Valid to", than "Price per case".

So, following this logic, based on the data below, the results should be like below:

"Forecast Month" = 3/1/2018, than "Price per case" = 16.8

"Forecast Month" = 4/1/2018, than "Price per case" = 20

Note: I attached an Excel file with this data below to make it easier if you want to try it.

Thanks in advance,

LD

1 Solution

Accepted Solutions
jwjackso
Contributor III

Re: Condition based on date range

Have you considered the IntervalMatch function (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...) in you load script?

The following has not been tested:

Data:

load * inline [

Material,Cases,ForecastMonth

300610,65719,04/01/2018

300610,57871,0301/2018

];

Price:

load * inline [

From,To,Price,Material

01/01/2011,03/31/2018,16.8,300610

04/01/2018,12,31,999,20,30060

];

Inner join IntervalMatch(ForecastMonth,Material)

load From,To,Material

Resident Price;

4 Replies
jwjackso
Contributor III

Re: Condition based on date range

Have you considered the IntervalMatch function (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...) in you load script?

The following has not been tested:

Data:

load * inline [

Material,Cases,ForecastMonth

300610,65719,04/01/2018

300610,57871,0301/2018

];

Price:

load * inline [

From,To,Price,Material

01/01/2011,03/31/2018,16.8,300610

04/01/2018,12,31,999,20,30060

];

Inner join IntervalMatch(ForecastMonth,Material)

load From,To,Material

Resident Price;

luizcdepaula
Contributor II

Re: Condition based on date range

Hi Jerry,

First, thank you for the quick response and trying to help me figure this out.

However, I need a more dynamic solution. Maybe I have given a simple example.

I have a logic in Qlik where I pull cases and List Price per case from the system, and calculate the Dollar value, by multiplying them. However, the List Price might change once in a while, for several products, like hundreds. So, I need a logic based on the data range of the List Price, so that Qlik uses the List Price for that specific date range, Valid from and Valid to.

Example:

If "Forecast Month" (First day of the month) falls into the date range of the List Price, give me the List Price for that date range. I could pull the most current List Price, however we need to keep the old ones for historical transactions.

Let me know if you have other ideas,

Thanks

LD

jwjackso
Contributor III

Re: Condition based on date range

You would populate the data for the IntervalMatch from your database, not an inline load as in my example.  You would have the historical prices still attached to records where the from/to date range was appropriate.

luizcdepaula
Contributor II

Re: Condition based on date range

Jerry,

Thanks for your help. The IntervalMarch did the work, exactly like you mentioned before. I used a Left Join after the load, in order to create a table I could save a use it for future calculation in the script. The final script it as follows:

LIST_PRICE:

LOAD

    MATNR,

    LIST_PRICE,

    Valid_To,

    Valid_From

FROM LIST_PRICE_USD.qvd;

FORECAST:

LOAD

    MATNR,

    Forecast,

    fDate,

FROM FORECAST.qvd;

Inner join

IntervalMatch (fDate,[MATNR]) Load Valid_From, Valid_To,MATNR Resident LIST_PRICE ;

Left Join

Load

    MATNR,

    LIST_PRICE,

    Valid_To,

    Valid_From

Resident LIST_PRICE;

Drop Table LIST_PRICE;

Thanks again!

LD

Community Browser