Should I Use Join/ Concatenate/Keep to create a combined tabled??

[amanjain57]

I have an interesting use case as you see below.

Need is to combine the File 1 and File 2 to generate a final table as shown below. Please refer to the attached file for more clarity.

There are some conditions when making the final table..

1. When Measure 1 is present , then only the Final Reason is valid. Therefore Material E has a Final Reason- N/A.
2. When Reason Code is 1026 or 1046 in File 1 , then we go to the File 2 to find out the Final Reason from the Text Column.
3. In File 2, Material X is not present in File 1 so it is not considered in the final table.
4. In general if a material is present in File 1 with the Reason Code 1026/1046 only then you go to File 2. The Amount in File 2 for a particular material is mostly a subset of Measure 1. Example Material A has 5$ in File 1 as Measure 1 and the total of Material A in file 2 is 5$. So the total amount in File 2 <= Measure 1.
5. Total measure = Measure 1+ Measure 2+ Measure 3+Measure 4

File 1									File 2
Material	Plant Code	Reason Code	Measure 1	Measure 2	Measure 3	Measure 4	Total Measure		Material	Plant Code	Reason Code	Text	Amount
A	101	1026	5				5		A	101	1026	M303	2.5
B	102	1046	10				10		A	101	1026	M304	2.5
C	103	M201	3				3		B	102	1046	S101	5
D	104	M101	2		3		5		B	102	1046	S102	2.5
E	105	M301				1	1		B	102	1046	X103	1.5
Totals			20	0	3	1	24		X	104	1026	M101	2
									X	104	1026	M102	3
Final Table
Material	Plant Code	Reason Code	Final Reason	Measure 1	Measure 2	Measure 3	Measure 4	Amount	Total Measure
A	101	1026	M303	5				2.5	5
A	101	1026	M304	5				2.5	5
B	102	1046	S101	10				5	10
B	102	1046	S102	10				2.5	10
B	102	1046	X103	10				1.5	10
C	103	M201	M201	3				3	3
D	104	M101	M101	2		3		2	5
E	105	M301	M301				1	-	1
Totals				20	0	3	1	19	24
or
Material	Plant Code	Reason Code	Final Reason	Measure 1	Measure 2	Measure 3	Measure 4	Amount	Total Measure
A	101	1026	M303	5				2.5	5
A	101	1026	M304	5				2.5	5
B	102	1046	S101	10				5	10
B	102	1046	S102	10				2.5	10
B	102	1046	X103	10				1.5	10
C	103	M201	M201	3				3	3
D	104	M101	M101	2		3		2	5
E	105	M301	N/A				1	-	1
Totals				20	0	3	1	19	24

I am stuck down how to create this table.

I had tried to use Join, concatenate etc. but may be I am not using it in the right way.

Any help is appreciated.

[Miguel_Angel_Baeyens]

As a rule of thumb, if tables have the same columns, you can use CONCATENATE. Also, if you need to distinguish in the resulting table the source table of each.

If there is a relation between both tables by one or more fields, you can use JOIN.

KEEP does not make one table out of two, but reduces the table based on the KEEP condition.

[YoussefBelloum]

Hi,

on the first condition: When Measure 1 is present , then only the Final Reason is valid..

Final Reason is not a field present at the source tables (table1 or table2), so it is a created field.. how do you create that field ? calculation rule ?

[amanjain57]

Final Reason = Reason Code (if not 1026/1046)

Final Reason = text (if =1026/1046)

Does that help?

[YoussefBelloum]

here is one solution,

for me, left join is the best solution to your problem. (because on the final table, the reconds are based on the keys of the table1)

I didn't use keep or concatenate because:

Concatenate will add the rows from one table to another and since your two table don't have the same fields (one have Measures from 1 to 4, and the second have the text and the amount), this approach is not suitable for your need.

Keep will keep the tables separate, so it is an additional physical table in the model..

PFA

[amanjain57]

Hi Can you please send me the code or the QVF??

I am using Qliksense so the QVW will not work for me.

[amanjain57]

Understood, but none of the cases is working for me.

[YoussefBelloum]

Sorry, I didnt pay attention.. I will upload a QVF later

[santho_ak]

Here is the qvf. Can somebody take it further. I will also try from my end.

[PrashantSangle]

try below,

File1:

Load Material,

Plant_Code,

If(Isull(Measure_1) or len(trim(Measure_1))=0,'N/A',Reason_Code) as Reason_Code,

Measure_1,

Measure_2,

Measure_3,

Measure_4

from File1;

Left Join (File1)

Load Material,

Plant_Code,

Reason_Code,

Text,

Amount

from File2

where exists(Material,Material);

NoConcatenate

Final:

Load *,

Measure1 + Measure2 + Measure3 + Measure4  as Total_Measure

from File1;

Drop table File1;

Regards,