topic Re: Match multi value fields in QlikView

Match multi value fields

kfoudhaily — Wed, 25 Nov 2020 16:16:04 GMT

Hello qlikers,

I'm encoutring the following issue:

I need to create a link between two tables

* ventesDCOM: order lignes where each line has and order id, product id, date and estimated delivery: timerange

* Livraison: contains a simulation of delivery made by clients on web site but i don't have any order id; but I have a multivalue field with product ids composing the shopping basket (product id concatenated and comma separated)

I need to link each basket to the order id if it has the same componements.

please find data simple in attachement.

I have tried to load ventesDCOM grouped by order id and concatenate the product ids to reconstruct the basket, but this is not possible because i'm not having always the right order to identify correctly the fields.

please also find app in attachement.

Can you please help?

Thank you very much.

Regards,

stalwar1‌

youssefbelloum‌

marcus_sommer‌

Re: Match multi value fields

ogautier62 — Wed, 25 Jul 2018 10:26:58 GMT

Hi,

not simple :

how do you do with duplicate ?

and when there's only 1 item : full of duplicate !

and sometimes not same inseecode (delivery city) so 2 differents orders, but which one choose ?

regards

Re: Match multi value fields

sunny_talwar — Wed, 25 Jul 2018 11:13:14 GMT

I am not entirely sure what the issue is? Can you pick an example selection and explain what the expected output needs to look like once loaded into the dashboard?

Re: Match multi value fields

kfoudhaily — Wed, 25 Jul 2018 11:57:54 GMT

I need to get an "ORDERS_ID" for a combination of filed "codic"

create a link between the two tables

Re: Match multi value fields

kfoudhaily — Wed, 25 Jul 2018 12:00:11 GMT

it doesn't matter if theres is more than one record that can match.
i'm not looking to create a join, just a logical link between the tables to evaluate if simulations of deliveries much with reality if the basket is validated.

please note that it's ok to have more simulations than valid baskets.

regards,

Re: Match multi value fields

kfoudhaily — Wed, 25 Jul 2018 12:53:51 GMT

it's somthing link the followgin but I couldn't figuer it out;

https://community.qlik.com/message/1069475?&_ga=2.126625549.1403226436.1532520715-1448455784.1527599977#1069475

Re: Match multi value fields

sunny_talwar — Wed, 25 Jul 2018 13:13:10 GMT

But why are you concatenating the PARTNUM to create codic... why can't you let it be codic and let the two join on it's own?

Re: Match multi value fields

sunny_talwar — Wed, 25 Jul 2018 13:14:41 GMT

I mean, why can't you do this

LOAD

ORDERITEMS_ID,

ORDERS_ID,

PARTNUM as codic, // = codic

STATUS,

LASTCREATE,

NUMCMD,

TIMERANGE,

DELIVERYDATE,

SHIPPINGDATE

FROM

sample_data.xlsx

(ooxml, embedded labels, table is ventesDCOM);

LOAD date,

heure,

marque,

canal,

codic,

insee,

dateLivraison,

delaiLivraison,

setUpService

FROM

(ooxml, embedded labels, table is Livraison);

Re: Match multi value fields

sunny_talwar — Wed, 25 Jul 2018 13:16:10 GMT

Or this

LOAD ORDERITEMS_ID,

ORDERS_ID,

PARTNUM as codic, // = codic

STATUS,

LASTCREATE,

NUMCMD,

TIMERANGE,

DELIVERYDATE,

SHIPPINGDATE

FROM

sample_data.xlsx

(ooxml, embedded labels, table is ventesDCOM);

LOAD date,

heure,

marque,

canal,

SubField(codic, ',') as codic,

insee,

dateLivraison,

delaiLivraison,

setUpService

FROM

[sample_data.xlsx]

(ooxml, embedded labels, table is Livraison);

Re: Match multi value fields

kfoudhaily — Wed, 25 Jul 2018 14:12:03 GMT

the problem is that I don't knwo how to reconstruct the field codic afterwards.

when makes the line codic unique is the combination of these specefic product ids.

But I thought about creating an autonumber first to identify thses codic and then finish as you recomand.

Thank you sunny, you're always a great help.

regards,

Re: Match multi value fields

kfoudhaily — Fri, 27 Jul 2018 07:35:10 GMT

here the script i finaly used:

Let vAffiniteMint =00; //definir l'affinite de filtrage en minutes

Let vAffiniteSec =10; //definir l'affinite de filtrage en secondes

ventesDCOM:

LOAD

ORDERITEMS_ID,

ORDERS_ID,

PARTNUM, // = CODIC

// STATUS, // Statut de la

// LASTCREATE, // Date de création de la commande

Timestamp(date#(LASTCREATE,'DD/MM/YYYY hh:mm')) as timestamp_lastcreate, //conversion en timestamp

Timestamp(date#(LASTCREATE,'DD/MM/YYYY hh:mm')-MakeTime(00,$(vAffiniteMint),$(vAffiniteSec))) as timestamp_lastcreate_start,//interval min - tolérance 10 secondes

Timestamp(date#(LASTCREATE,'DD/MM/YYYY hh:mm')+MakeTime(00,$(vAffiniteMint),$(vAffiniteSec))) as timestamp_lastcreate_end, //interval max - tolérance 10 secondes

// NUMCMD, // Numero de commande dans les systèmes legacy (provenant de Mainframe)

TIMERANGE, //créneau de livraison proposé JO =Jounée / MA = Matin / AM / SO / S1 / S2/ S3

// DELIVERYDATE,

// TIMERANGE, // Colonne présente 2 fois dans le fichier souce

SHIPPINGDATE //Date de livraison

// DATE_TOPE, //Date de livraion effectuée

// SHIPPING_START_HOUR, // heure de début du créneau de livraison

// SHIPPING_END_HOUR //heure de fin du créneau de livraison

FROM

ventesDCOM*.csv

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

// CALCUL DU NB d'ARTICLES PAR COMMANDE

inner join

LOAD ORDERS_ID,

count(PARTNUM) as Nb_Articles_DCOM

RESIDENT ventesDCOM

GROUP BY ORDERS_ID;

//RECONSTITUTION D'UNE LIGNE PANIER

inner join

LOAD ORDERS_ID,

text(Concat(PARTNUM,',')) as panier_DCOM

Resident ventesDCOM

GROUP BY ORDERS_ID;

//SUPPRESSION DU CODE ARTICLE UNIRAIE (PARTNUM et ORDERITEMS_ID)

DROP Fields PARTNUM, ORDERITEMS_ID;

///$tab Livraison

// on charge la table livraison - on crée une clé unique du codic pour la reconstruction

Livraison:

LOAD

AutoNumber(codic&date&heure&insee) as key_codic, // clé necessaire pour reconstruire le panier

// date,

// heure,

Timestamp(date&' '&heure,'DD/MM/YYYY hh:mm:ss') as timestamp_livraison,

// marque,

// canal,

codic,

SubField(codic,',') as codic_split,

insee,

dateLivraison,

delaiLivraison

// setUpService

FROM

Livraison*.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//concaténation des codic pour reprendre le bon ordre en utilisant la clé déjà crée

inner join

LOAD key_codic,

text(Concat(codic_split,',')) as panier_DCOM // Panier_Livraison = panier_DCOM 1er point de jointure

resident Livraison

Group by key_codic;

Drop Fields codic_split,codic,key_codic;

//EXIT SCRIPT;

///$tab RAPPROCHEMENT

Inner Join //avec table livraison

IntervalMatch (timestamp_livraison, panier_DCOM) //interval match etendu sur deux clés

Load //load des champs clés pour jointure avec table livraison

timestamp_lastcreate_start, //borne inferieure interval: clé sec1

timestamp_lastcreate_end, //borne supérieure interval: clé sec2

panier_DCOM //clé principale

Resident ventesDCOM; //fin interval match

LEFT JOIN //on reconstruit la table ventes à partir de la table précédante

LOAD * RESIDENT ventesDCOM;

DROP TABLE ventesDCOM; //ensuite on drop table vente d'origine

///$tab exit

EXIT SCRIPT;