Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kfoudhaily
Partner - Creator III
Partner - Creator III

Match multi value fields

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

QlikView Qlik Sense consultant
1 Solution

Accepted Solutions
sunny_talwar

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);

View solution in original post

10 Replies
ogautier62
Specialist II
Specialist II

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

sunny_talwar

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?

kfoudhaily
Partner - Creator III
Partner - Creator III
Author

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

create a link between the two tables

Capture.PNG

QlikView Qlik Sense consultant
kfoudhaily
Partner - Creator III
Partner - Creator III
Author

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,

QlikView Qlik Sense consultant
kfoudhaily
Partner - Creator III
Partner - Creator III
Author

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.1527599...

QlikView Qlik Sense consultant
sunny_talwar

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?

sunny_talwar

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);

sunny_talwar

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);

kfoudhaily
Partner - Creator III
Partner - Creator III
Author

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,

QlikView Qlik Sense consultant