Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

amanjain57
Contributor

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

  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 CodeReason CodeMeasure 1Measure 2Measure 3Measure 4Total Measure Material Plant CodeReason CodeTextAmount
A10110265 5 A1011026M3032.5
B102104610 10 A1011026M3042.5
C103M2013 3 B1021046S1015
D104M1012 3 5 B1021046S1022.5
E105M301 11 B1021046X1031.5
Totals 2003124 X1041026M1012
X1041026M1023
Final Table
Material Plant CodeReason CodeFinal ReasonMeasure 1Measure 2Measure 3Measure 4AmountTotal Measure
A1011026M3035 2.55
A1011026M3045 2.55
B1021046S10110 510
B1021046S10210 2.510
B1021046X10310 1.510
C103M201M2013 33
D104M101M1012 3 25
E105M301M301 1-1
Totals 200311924
or
Material Plant CodeReason CodeFinal ReasonMeasure 1Measure 2Measure 3Measure 4AmountTotal Measure
A1011026M3035 2.55
A1011026M3045 2.55
B1021046S10110 510
B1021046S10210 2.510
B1021046X10310 1.510
C103M201M2013 33
D104M101M1012 3 25
E105M301N/A 1-1
Totals 200311924

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.

9 Replies

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

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
Esteemed Contributor

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

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
Contributor

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

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

Final Reason = text (if =1026/1046)

Does that help?

YoussefBelloum
Esteemed Contributor

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

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
Contributor

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

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

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

amanjain57
Contributor

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

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

YoussefBelloum
Esteemed Contributor

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

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

sashokku
Contributor

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

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

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

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Community Browser