How to link perfectly 2 different fields containing divergent values?
I have to connect 2 different fields containing divergent values. I explain:
We are in an industrial area.
We have numbers for the Quotation (for example: 100000v0) and when we have an Order, a number of Order is created/generated (for example: s60000/00). This number of Order (s60000/00 = field "OPRE_DOSSIER") is linked to the accepted Quotation (100000v0 = field "ELDV_ENDVUNIQ"). Both fields are in the table "FD_ENTETE_DEVI" (see the printscreen below).
Now, Quotations are detailled into different elements. First element is 100000v0-1, the second one is 100000v0-2, and so on. This subdivision of the Quotation is available in the table "FD_ELEM_DEVIS" where we find the general num of Quotation (see above, field "ELDV_ENDVUNIQ" = 100000v0) and the num of each element "ELDV_CODEUNIQ" (100000v0-1 and 100000v0-2). Thus for this example, in table "FD_ELEM_DEVIS", I have :
On the other side, I repeat: when we have an Order, on basis of the Quotation, a num of Order is created/generated.
The different elements of quotation are "duplicated" into s60000/00-1 (element 1) and s60000/00-2 (element 2).
But except for the generation, there is no table (I get this information from our system support) that link element 1 of the Quotation with element 1 of the Order (see the printscreen below)
And when we tried to generate a Qlikview report of operators' productivity (for example) with information available in the Quotation, we have a misleading information by element : element 2 s60000/00-2 has information about element 1 100000v0-1 and about element 2 100000v0-2. Because operators work on each element and the report is based on each element, not on the global Order.
And in reality element 1 for example is the same : 100000v0-1 = s60000/00-1.
Thank you for your help. And if it is not clear enough, I can explain more if you want.