topic Re: Extended interval match help in QlikView

Extended interval match help

Tue, 11 Nov 2014 00:44:06 GMT

I need help below:

Purchase:

Load * Inline

[

Supplier, Purchase

ABC, 125

DEF, 115

];

Discount:

Load * Inline

[

Supplier, From, To, Discount

ABC, 100, 119, 2%

ABC, 120, 129, 3%

ABC, 130, 150, 4%

DEF, 110, 119, 4%

DEF, 120, 129, 6%

DEF, 130, 150, 8%

];

Code here will be :

INNER JOIN (Purchase)

IntervalMatch (Purchase,Supplier) Load From, To, Supplier Resident Discount;

Join (Purchase) Load * Resident Discount;

Drop Table Discount;

Drop Fields From,To;

To explain my scenario, I am modifying above example.

I want the discount to be displayed with respect to each supplier.

Supplier1 | Discount1| Supplier2|Disocunt2|......................

Purchase:

Load * Inline

[

Supplier1,Supplier2, Supplier3,Purchase

ABC, XYZ,EWQ,125

DEF,FRT, YUT,115

];

Discount:

Load * Inline

[

Supplier, From, To, Discount

ABC, 100, 119, 2%

ABC, 120, 129, 3%

ABC, 130, 150, 4%

DEF, 110, 119, 4%

DEF, 120, 129, 6%

DEF, 130, 150, 8%

XYZ, 110,130,5%

XYZ, 100,120,5%

FRT,110,140, 2%

FRT,110,112, 2%

EWQ,110,140, 2%

YUT,105,118,6%

];

Wondering how to depict this in current code?

Re: Extended interval match help

anbu1984 — Tue, 11 Nov 2014 03:51:46 GMT

Check this qvw

Re: Extended interval match help

jagan — Tue, 11 Nov 2014 04:30:14 GMT

Hi,

Try this script

Temp:

Load * Inline

[

Supplier1,Supplier2, Supplier3,Purchase

ABC, XYZ,EWQ,125

DEF,FRT, YUT,115

];

Purchase:

LOAD

Supplier1 AS Supplier,

Purchase

RESIDENT Temp;

Concatenate(Purchase)

LOAD

Supplier2 AS Supplier,

Purchase

RESIDENT Temp;

Concatenate(Purchase)

LOAD

Supplier3 AS Supplier,

Purchase

RESIDENT Temp;

DROP TABLE Temp;

Discount:

Load * Inline

[

Supplier, From, To, Discount

ABC, 100, 119, 2%

ABC, 120, 129, 3%

ABC, 130, 150, 4%

DEF, 110, 119, 4%

DEF, 120, 129, 6%

DEF, 130, 150, 8%

XYZ, 110,130,5%

XYZ, 100,120,5%

FRT,110,140, 2%

FRT,110,112, 2%

EWQ,110,140, 2%

YUT,105,118,6%

];

INNER JOIN (Purchase)

IntervalMatch (Purchase, Supplier) Load From, To, Supplier Resident Discount;

LEFT Join (Purchase) Load * Resident Discount;

Data:

NoConcatenate

LOAD

DISTINCT *

Resident Purchase;

DROP TABLE Purchase;

Drop Table Discount;

Drop Fields From,To;

Regards,

Jagan.