Intervalmatch duplicates my rows

Fri, 29 Mar 2013 16:49:03 GMT

i HAVE A CATALOG: EACH PRODUCT CAN HAVE DIFFERENT PRICES FOR EVERY CLIENT, AND ALSO DIFFERENT PRICES FOR THE SAME CLIENT BECAUSE THEY CAN CHANGE DURING THE TIME.

i NEED TO OBTEIN THE VALUE (PRICE * QUANTITY) OF A PRODUCT VALID IN THE PERIOD WHEN IT WAS SOLD TO A CLIENT.

THE RESULT I'VE OBTEINED IS THE VALUE FOR EVERY PRICE OF THE PRODUCT, INSTEAD!

/************this is the script************/

CATALOG_1:

//LOAD* WHERE EXISTS(KEY);

load *,

CLIENT_CODE&PRODUCT_ CODE AS KEY;

select

CLISML AS CLIENT_CODE,

CDARML AS PRODUCT_ CODE,

PRZLML AS PRICE,

DTUAML AS STARTING_DATE,

DTLVML AS EXPIRING_DATE

FROM MGLIS01F

FT:

LOAD *,

CLIENT&PRODUCT AS KEY;

select

CLIENT,

PRODUCT,

DTBOFM AS DATE,

QUANTITY

from FT;

intervalmatch (DATE,KEY) load * resident CATALOG_1;

/**********end*********/

//the final resul is this:

//client| product | date | price| value

// (the price is that one between starting and expiring date)

//I've also one more question:

//I have CATALOG_1 and CATALOG_2:

//I need to chose CATALOG_2 only if CATALOG_1 is not avalaible: have I to use a double "intervalmatch"??

//CATALOG_2 has the same structure of the previous one,

THANKS TO EVERYBODY!§!

topic Intervalmatch duplicates my rows in QlikView

Intervalmatch duplicates my rows