Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

getting the right values for the right code and time period

Hi,

i'm having some troubles linking 2 tables. This is the situation:

Table1:

- PrestationCode
- RequestTS

Table2:

- PrestationCode

- startTS

- endTS

- Value

Each PrestationCode can be used multiple times and has values for a certain time period (startTS - endTS). I first just created a concatenated link PrestationCode&'-'&monthstart(RequestTS) with PrestationCode&'-'&startTS. It worked in must cases cause startTS was in most cases the first of the month but sometimes it's not the first and i can't just set startTS to the first of the month cause then i'll get wrong values.

example:

code: 5000 startTS: 1/07/2007 endTS: 31/12/2007 values: 10, 20
code: 5000 startTS: 11/07/2007 endTS: 30/06/2008 values: 5

code: 1000 startTS: 1/01/2007  endTS: 31/12/2009 values: 15

if i select a code and startTS than i'll get a unique endTS so i don't think i need an intervalmatch... I just need to see if there is more then one startTS in that month.

I hope you guys understand my situation and can help me

1 Solution

Accepted Solutions
gandalfgray
Not applicable

Re: getting the right values for the right code and time period

Hi

I would do something like:

//find the correct startTS

Left Join (Table1)

Intervalmatch (RequestTS, PrestationCode)

Load startTS,

    endTS,

    PrestationCode

resident Table2;

// Join the Value

Left Join (Table1)

Load startTS,

    PrestationCode,

    Value

Resident Table2;

Drop Table Table2; // Drop when you finished with it, otherwise you get syntetic keys

You can also drop fieds startTS and endTS at this stage if you want to.

3 Replies
Not applicable

getting the right values for the right code and time period

Its something like this, i select a Requestts and it needs to link with a date in startTS but as you can see for that time there isn't one so it should link to the value smaller and closest to the requestTS in this case: 01/01/2007. Just to make sure it's not always the first of the month for startTS it's just a coincidence.

compare.PNG

gandalfgray
Not applicable

Re: getting the right values for the right code and time period

Hi

I would do something like:

//find the correct startTS

Left Join (Table1)

Intervalmatch (RequestTS, PrestationCode)

Load startTS,

    endTS,

    PrestationCode

resident Table2;

// Join the Value

Left Join (Table1)

Load startTS,

    PrestationCode,

    Value

Resident Table2;

Drop Table Table2; // Drop when you finished with it, otherwise you get syntetic keys

You can also drop fieds startTS and endTS at this stage if you want to.

Not applicable

Re: getting the right values for the right code and time period

Thanks for helping! It didn't fix the problem from the start cause i had some old data which was messing with my values but i did a where year(startTS) > '2005' and then i got the right values thanks