topic Re: Intervalmatch in QlikView

Intervalmatch

Tue, 05 Feb 2013 07:52:26 GMT

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.

Re: Intervalmatch

hic — Tue, 05 Feb 2013 08:37:54 GMT

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

Re: Intervalmatch

Tue, 05 Feb 2013 08:54:07 GMT

Thank you very much, I will try.

Re: Intervalmatch

Tue, 05 Feb 2013 08:59:22 GMT

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

Re: Intervalmatch

Tue, 05 Feb 2013 09:12:04 GMT

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

How can I have only 3 rows ?

Thank you

Re: Intervalmatch

Tue, 05 Feb 2013 11:33:00 GMT

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

Re: Intervalmatch

Miguel_Angel_Baeyens — Tue, 05 Feb 2013 11:42:30 GMT

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

Re: Intervalmatch

Tue, 05 Feb 2013 12:27:44 GMT

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.

Re: Intervalmatch

Tue, 05 Feb 2013 12:50:24 GMT

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.