Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

melissapluke
New Contributor II

Finding a date range within a script

Hi all,

I am trying to accomplish the below issue within the load script. I am able to doing this using the FirstSortedValue function within a chart in the UI, but am not able to figure it out within a script.  I have the following situation:

Table A:

Key          DateStart          DateEnd          Rate

ABCD     1/1/2016            1/31/2016        $10

ABCD     1/1/2017            1/31/2017        $8

WXYZ     1/1/2016            1/31/2016        $11

WXYZ     1/1/2017            1/31/2017        $12

Table B:

ID          Key     Date

1          ABCD     7/15/2016

2          ABCD     12/30/2017

3          ABCD     1/1/2018

4          WXYZ     2/2/2016

I need return the appropriate rate for each ID.

This is the table I am expecting.

Table B:

ID          Key     Date                    Rate

1          ABCD     7/15/2016          $10

2          ABCD     12/30/2017        $8

3          ABCD     1/1/2018              -

4          WXYZ     2/2/2016          $11

I've tried using applymap, but because of the multiple date range, I can't seem to figure it out. Any help would be appreciated. Thank you!

1 Solution

Accepted Solutions
thiago_justen
Valued Contributor III

Re: Finding a date range within a script

Melissa, let's try something new:

Table_A_Temp:

LOAD * Inline [

Key,     DateStart,          DateEnd,         Rate

ABCD,    1/1/2016,            31/12/2016,        10

ABCD,    1/1/2017,            31/12/2017,        8

WXYZ,    1/1/2016,            31/12/2016,        11

WXYZ,    1/1/2017,            31/12/2017,        12

];

Table_B:

LOAD

*,

    Key&Date AS CompoundKey;

LOAD * Inline [

ID,        Key,    Date

1,          ABCD,     15/07/2016

2,          ABCD,     30/12/2017

3,          ABCD,     1/1/2018

4,          WXYZ,     2/2/2016

];

Bridge:

IntervalMatch(Date,Key)

Load

DateStart,

    DateEnd,

    Key

Resident Table_A_Temp;

Left Join (Table_A_Temp)

Load

*

Resident Bridge;

Drop Table Bridge;

Table_A:

Load

*,

    Key&Date AS CompoundKey

Resident Table_A_Temp;

Drop Table Table_A_Temp;

Drop Fields Date,Key From Table_A;

The result, as you need, is below:

Capturar.PNG

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
11 Replies
Luminary
Luminary

Re: Finding a date range within a script

Use Extended intervalmatch prefix

thiago_justen
Valued Contributor III

Re: Finding a date range within a script

Try this:

Inner Join

IntervalMatch (Date,Key)

LOAD

      DateStart,

      DateEnd,

      Key

Resident TableA;

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
melissapluke
New Contributor II

Re: Finding a date range within a script

This works but ends up creating a synthetic key. If I drop the fields from the original table, it no longer works for me.

thiago_justen
Valued Contributor III

Re: Finding a date range within a script

Melissa,

Could you please share this piece of script?

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
melissapluke
New Contributor II

Re: Finding a date range within a script

Sure, thanks!

Here you go:

[Data]:

Load

   Key,

   "ID",

    "Date",

From xyz;

Contact:

Load

     Key,

     Contact,

     "Effective Date",

     "Effective Date End",

     Rate

From abc;

Inner Join ([Data])

IntervalMatch ("Date",Key)

LOAD

     "Effective Date",

    "Effective Date End",

     Key

Resident Contact;

thiago_justen
Valued Contributor III

Re: Finding a date range within a script

Try this:

[Data]:

Load

   Key,

   "ID",

    "Date",

From xyz;

Contact:

Load

     Key,

     Contact,

     "Effective Date",

     "Effective Date End",

     Rate

From abc;

Inner Join //omit the table

IntervalMatch ("Date",Key)

LOAD

     "Effective Date",

    "Effective Date End",

     Key

Resident Contact;

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
melissapluke
New Contributor II

Re: Finding a date range within a script

Hi,

Thanks, the issue with this solution is that is sometimes I need to view ALL the contact records, whether they are in the Data table or not. Doing this inner join seems to limit the Contacts table to only what is in the Data table. 

thiago_justen
Valued Contributor III

Re: Finding a date range within a script

Melissa, let's try something new:

Table_A_Temp:

LOAD * Inline [

Key,     DateStart,          DateEnd,         Rate

ABCD,    1/1/2016,            31/12/2016,        10

ABCD,    1/1/2017,            31/12/2017,        8

WXYZ,    1/1/2016,            31/12/2016,        11

WXYZ,    1/1/2017,            31/12/2017,        12

];

Table_B:

LOAD

*,

    Key&Date AS CompoundKey;

LOAD * Inline [

ID,        Key,    Date

1,          ABCD,     15/07/2016

2,          ABCD,     30/12/2017

3,          ABCD,     1/1/2018

4,          WXYZ,     2/2/2016

];

Bridge:

IntervalMatch(Date,Key)

Load

DateStart,

    DateEnd,

    Key

Resident Table_A_Temp;

Left Join (Table_A_Temp)

Load

*

Resident Bridge;

Drop Table Bridge;

Table_A:

Load

*,

    Key&Date AS CompoundKey

Resident Table_A_Temp;

Drop Table Table_A_Temp;

Drop Fields Date,Key From Table_A;

The result, as you need, is below:

Capturar.PNG

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
thiago_justen
Valued Contributor III

Re: Finding a date range within a script

If you want to check my example app, here it is. !

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
Community Browser