Should I Use Join/ Concatenate/Keep to create a combined tabled??
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..
When Measure 1 is present , then only the Final Reason is valid. Therefore Material E has a Final Reason- N/A.
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.
In File 2, Material X is not present in File 1 so it is not considered in the final table.
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.
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.
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.
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 ?
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..
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,
Great dreamer's dreams never fulfilled, they are always transcended. Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
.png "Miguel_Angel_Baeyens"){kind=avatar}
