Match multi value fields

[kfoudhaily]

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‌

[ogautier62]

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]

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

create a link between the two tables

[kfoudhaily]

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,

[kfoudhaily]

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

[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 · Accepted solution]

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]

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,