IntervalMatch issue

[yohann_w]

Hello,

I encountering issue with the intervalmatch function.

I try to add the data FLG_PLAN_DE_STOCK_NATIONAL on line of sales.

FLG_PLAN_DE_STOCK_NATIONAL is an attribut of COD_PRODUIT with start date and end date relating to the order date (DAT_COMMANDE_INITIALE).

I'm using this :

PDS_NAT:
LOAD Distinct
COD_PRODUIT,
FLG_PLAN_DE_STOCK_NATIONAL,
date(DAT_DEBUT) as DAT_DEBUT,
if(isnull(DAT_FIN),date(today()-1),date(DAT_FIN)) as DAT_FIN
FROM [lib://QVD_Models/DATAWH_PLAN_STOCK_NATIONAL.QVD]
(qvd);

NoConcatenate

VENTES:
LOAD
COD_CLE_LIGNE_COMMANDE_CLIENT_HIST,
COD_PRODUIT,
MNT_ENREGISTRE_CA_VENTE,
MNT_VALIDEE_CA_VENTE,
MNT_FACTURE_CA_VENTE,
DAT_COMMANDE_INITIALE
FROM [lib://QVD_Models/DATAWH_VENTES/DATAWH_VENTES_202103.QVD]
(qvd);


Left Join IntervalMatch (DAT_COMMANDE_INITIALE,COD_PRODUIT) LOAD DAT_DEBUT, DAT_FIN, COD_PRODUIT resident PDS_NAT;

drop Table PDS_NAT;

There is no script error on loading but the field FLG_PLAN_DE_STOCK_NATIONAL is not present on the app :

Could you help me ? 😞

[Dalton_Ruer]

Because you don't ask for the field in the join and then you drop the table that the field was in. 

[yohann_w]

But when adding the field, like this :

Left Join IntervalMatch (DAT_COMMANDE_INITIALE,COD_PRODUIT) LOAD DAT_DEBUT, DAT_FIN, COD_PRODUIT, FLG_PLAN_DE_STOCK_NATIONAL resident PDS_NAT;

There is an error 😕

At the end i drop the table "PDS_NAT" because i just want to keep my fact's table "VENTES" with the field i require "FLG_PLAN_DE_STOCK_NATIONAL"

[mato32188]

Hi @yohann_w ,

you need to keep only three fields in your load after IntervalMatch.

Field FLG_PLAN_DE_STOCK_NATIONAL join/map afterwards.

BR

m

[yohann_w]

@mato32188  Thanks but i don't understand how to join my field after that code :s

[mato32188 · Accepted solution]

@yohann_w ,

I am not sure if I am 100% correct as writing on the fly, but you can try:

PDS_NAT:
LOAD Distinct
COD_PRODUIT,
FLG_PLAN_DE_STOCK_NATIONAL,
date(DAT_DEBUT) as DAT_DEBUT,
if(isnull(DAT_FIN),date(today()-1),date(DAT_FIN)) as DAT_FIN
FROM [lib://QVD_Models/DATAWH_PLAN_STOCK_NATIONAL.QVD]
(qvd);

MAP:
Mapping LOAD Distinct
COD_PRODUIT &'|'& date(DAT_DEBUT) &'|'& if(isnull(DAT_FIN),date(today()-1),date(DAT_FIN)),
FLG_PLAN_DE_STOCK_NATIONAL
Resident PDS_NAT;

NoConcatenate

VENTES:
LOAD
COD_CLE_LIGNE_COMMANDE_CLIENT_HIST,
COD_PRODUIT,
MNT_ENREGISTRE_CA_VENTE,
MNT_VALIDEE_CA_VENTE,
MNT_FACTURE_CA_VENTE,
DAT_COMMANDE_INITIALE
FROM [lib://QVD_Models/DATAWH_VENTES/DATAWH_VENTES_202103.QVD]
(qvd);

Left Join

IntervalMatch (DAT_COMMANDE_INITIALE,COD_PRODUIT) LOAD DAT_DEBUT, DAT_FIN, COD_PRODUIT resident PDS_NAT;

Final:

Load *,

ApplyMap('MAP',COD_PRODUIT &'|'& DAT_DEBUT&'|'& DAT_FIN,null()) as FLG_PLAN_DE_STOCK_NATIONAL

Resident VENTES;

drop table VENTES;

drop Table PDS_NAT;

m

[yohann_w]

@mato32188sorry for the answer delay, but indeed ! It works ! 😉