Intervalmatch

[#1]

Dear all, I’m quite new in qlikview and I have this kind of
problem.

I have two tables, the first is:

INVOICES:

  cod, date, value

  1001, 20/10/2012, 110

  1001, 18/11/2012, 500

  1005, 23/11/2012, 400

The second one is:

  MASTER:

  cod, date_from, date_to, attrubute

  1001, 01/10/2012, 05/11/2012, A

  1001, 06/11/2012, 31/12/2012, B

  1005, 01/10/2012, 31/12/2012, A

I’m using intervalmatch in order to assign the correct
attribute depending on time to INVOICES but I’m not able to have only one
table.

  At the end of the process I would like to have instead of
two tables linked, only one table or qvd file with this kind of structure:

INTERVALMATCH:

cod, date, value, attribute

  1001, 20/10/2012, 110, A

  1001, 18/11/2012, 500, B

  1005, 23/11/2012, 400, A

  Thank you in advance.

[hic]

If you load the three tables as three tables, you can still get a one-table-view in the UI.

INVOICES:
Load * From INVOICES;

Intervals:
Load * From MASTER;

IntervalMatchBridge:
IntervalMatch (date)
Load distinct date_from, date_to Resident Intervals;

And if you really want to squeeze all three tables into one, you should do the following:

INVOICES:
Load * From INVOICES;

Data:
Load * From MASTER;

Join (Data)
IntervalMatch (date)
Load distinct date_from, date_to Resident Data;

Join (Data)
Load * Resident INVOICES;

Drop Table INVOICES;

HIC

[#3]

Thank you very much, I will try.

[#4]

master:

LOAD //Distinct cod,

     date_from,

     date_to,

     attribute

FROM

C:\Users\vishwaranjan\Desktop\interval__match.xlsx

(ooxml, embedded labels, table is master);

invoices:

LOAD //cod,

     date,

     value

FROM

C:\Users\vishwaranjan\Desktop\interval__match.xlsx

(ooxml, embedded labels, table is invoices);

Data:

IntervalMatch(date)

LOAD date_from,

     date_to Resident master;

then output show like this

date	value	attribute
10/20/2012	110	A
11/18/2012	500	A
11/18/2012	500	B
11/23/2012	400	A

[#5]

Sorry, I have tried the script, but the result is:

How can I have only 3 rows ?

Thank you

[#6]

Thnak you, but at the end of the process I would like to have instead of

two tables linked, only one table or qvd file with this kind of structure (only 3 row based o data validity):

INTERVALMATCH:

cod, date, value, attribute

  1001, 20/10/2012, 110, A

  1001, 18/11/2012, 500, B

  1005, 23/11/2012, 400, A

How can i do this ?

Thanks

[Miguel_Angel_Baeyens]

Hi,

Check the code in this post. This uses DROP and JOIN to keep only one resulting table. This code uses KEEP and JOIN instead to maintain only one table as a result.

Hope that helps.

Miguel

[#8]

Thanks, the first post is my case, but due to the facts that data ranges are different based on customer_code (cod), how can I have this results:

cod, date, value, attribute

  1001, 20/10/2012, 110, A

  1001, 18/11/2012, 500, B

  1005, 23/11/2012, 400, A

Only 3 rows from Invoices with the correct attribute based on data renge in Master table ?

Thanks in advance.

[#9 · Accepted solution]

Hi,

I resolved this issue as follows.

I created a table IntervalMatch called "Interval"

I made the join between Invoice and Master.

Then I made the code a condition where a table should match the code of another table.

See the example.