Correlating values of a field depending on another field

[JIBERN]

Dear All, 

I am new to QlikSense and I'm trying to create a Mapping table. My raw data is the following one:

As you can see the field "MAT-LOTE" is the key in the relation between "#Orden" of different "Sección Semi". For exemple:

"#Orden" 1025312 and 1026042 are correlated as the field "MAT-LOTE" (620025-20001 in both cases) shows. 

I want to create a table with the following format:

Sometimes more than 1 "#Order" would be associated with the same "MAT-LOTE" and "Sección Semi". 

Thank you so much for you time. 

Best regards. 

[JuanGerardo]

Hi @JIBERN, I think this can make the trick:

Load
   MAT-LOTE,
   If([Sección-Semi]='ENT', [#Orden]) AS ENT,
   If([Sección-Semi]='FAT', [#Orden]) AS FAT
From lib://yourdata;
   

From this point, and depending on what you intend to do when you have more than one row for every MAT-LOTE, you can group results using Group By.

JG

[JIBERN]

Dear @JuanGerardo , 

Thank you so much!

It did work. My goal was to create a table that correlates orders from both "Sección-Semi" to then create a Mapping table, which I was able to do with no problems. 

Right now I have been encountering another problem. Sometimes I have more than one row for every MAT-LOTE (see below). 

(Sometimes the product manufactured (FAT section) is used in more than one packing order (ENT section).

What I am trying to do is to map the costs in the Fat orders into the ENT orders. So what I did was to LOAD the FAT orders mapping its name to the orders in the ENT section. The problem is that now, the costs associated to the 1029209 FAT order are only loaded into the ENT order 1029192. 

How can I "duplicate" my fat orders to be able to correlate again the costs from the FAT order to the other ENT order (1029200)?

Thank you so much. 

JI. 

[JuanGerardo]

I suppose you have to use join statements, like this:

Left Join(MatLoteTable)
Load
   ENT,
   ENTCost
Resident ENTTable;

Left Join(MatLoteTable)
Load
   FAT,
   FATCost
Resident FATTable;

So you will get the costs in your current MatLoteTable (the one in previous examples), for all rows.
JG