topic Re: IntervalMatch issue in App Development

IntervalMatch issue

yohann_w — Tue, 13 Apr 2021 17:51:56 GMT

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 ? 😞

Re: IntervalMatch issue

Dalton_Ruer — Wed, 14 Apr 2021 14:01:33 GMT

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

Re: IntervalMatch issue

yohann_w — Wed, 14 Apr 2021 14:07:48 GMT

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"

Re: IntervalMatch issue

mato32188 — Wed, 14 Apr 2021 14:14:09 GMT

Hi @yohann_w ,

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

Field FLG_PLAN_DE_STOCK_NATIONAL join/map afterwards.

Re: IntervalMatch issue

yohann_w — Wed, 14 Apr 2021 14:19:13 GMT

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

Re: IntervalMatch issue

mato32188 — Wed, 14 Apr 2021 14:27:08 GMT

@yohann_w ,

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

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;

Re: IntervalMatch issue

yohann_w — Thu, 08 Jul 2021 15:03:31 GMT

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