Loading data from table within date range

[#1]

Hello,

I have a main data table (Onderdelen) and a table (PPCO) like below.

I would like to add a field to Onderdelen named OND_JUR_PPCO and fill it with a 1 when the orderdate (OND_D_BESTEL) from Onderdelen lies between PPCO_INGANGSDATUM and PPCO_EINDDATUM from the PPCO table. The 2 tables share a key as shown below. Per matching key there can be more than one record in the PPCO table and therefore the daterange is neccesary to find the right record.

So If the keys from both tables match and the orderdate lies between the date range then add OND_JUR_PPCO to Onderdelen with 1 or else with 0.

    PPCO Table

    PPCO_OPDRGEVER & PPCO_MERKCODE & PPCO_SHB & PPCO_LEVERANCIER AS PPCO_KEY,

    PPCO_OPDRGEVER,

    PPCO_SHB,

    PPCO_MERKCODE,

    PPCO_VOLGNR,

    PPCO_INGANGSDATUM,

    PPCO_EINDDATUM,   

    PPCO_LEVERANCIER

I can add both of the dates from PPCO with a left join into the Onderdelen Table but then I have to reload this huge table again to perform the date request. There must be an easier way.

Please Advise.

Thanx

Michel

[datanibbler]

Hi Michel,

do you need the PPCO table (that is the huge one, is it?) as such or is it only the start- and enddate that you need?

If it's the former and you do need the table as such - well, still loading RESIDENT from it just two fields should not pose an issue? You can join those to the other table and you can build an IF_construction to generate one new field.

Another, probably the more elegant solution, would be using IntervalMatch() in a LOAD statement - basically, with that you can find out if a date lies in the range of a start- and an end-date (among other possibilities).

HTH

Best regards,

DataNibbler

[#3]

Well the huge table is the Onderdelen table. From the PPCO table I need just one field together with the start and end date. I will try the intervalmatch wich you suggested.

Thanx

[#4]

I have tried to fix it with IntervalMatch but I can not get it to work.

I added this to my script:

Intervalmatch:

IntervalMatch(OND_D_BESTEL)

LOAD Distinct PPCO_INGANGSDATUM, PPCO_EINDDATUM

Resident PPCO;

This comes after the two tables are loaded and linked with the PPCO_KEY. The result is a synthetic key.

I have the feeling that i could work now only I have to get it right.

[#5 · Accepted solution]

Problem solved, I created a new table with all the dates in between the different ranges and then joined it with the lange data table. I used a loop function shown below.

PPCO_JUR:

LOAD*,

   PPCO_KEY_TMP & ' ' & ReferenceDate AS PPCO_KEY;

LOAD

   PPCO_KEY_TMP,

   OND_JUR_PPCO,

   OND_JUR_PPCO_RECIPTOT,

Date(PPCO_INGANGSDATUM + IterNo() -1) AS ReferenceDate

Resident PPCO_JUR_TMP

While IterNo() <= PPCO_EINDDATUM - PPCO_INGANGSDATUM + 1;

Henric Cronström, Thanx for this document.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/09/02/loops-in-the-script

Michel