Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

How to compare two fields from two different tables ?

Hello,

I'm new to Qlik View, and I have a scripting question : I want to filter a field from a table according an other field in an other table. In other words :

I have :

Table A
Key_A_B
Date_A

Table B
Key_A_B
Date_B

And I want to modify Table A as follow :

Table A
Key_A_B
Date_A
New_Date_B

and New_Date_B is Date_B  where (Date_B < Date_A).

Currently, to do that, my script is very ugly and not optimized ! It's something like this :

-----------------------------------------------------------------------------------------------------------------------

// Step 1

Left Join([Table A])

Load *

Resident([Table B]);

// So at this step, I have Date_A and Date_B Table A.
// Now, I filter Date_B by Date_A to obtain New_Date_B

// Step 2

Right Join ([Table A])

Load

    Date_B as New_Date_B

Resident([Table A])

where(Date_B < Date_A);

-----------------------------------------------------------------------------------------------------------------------


But this last step is very ugly, i don't find it very powerful (it takes too much time in script loading which is not a positive sign!)

In fact, i would like to do it in a more simply way (may be in one step), but to do that I need to directly compare Table A with Table B.
I tried something like that :

Left Join([Table A])

Load

     Date_B as New_Date_B

Resident([Table B])

where(Date_B < Date_A);


But it doesn't work because of Date_B which is not loading yet in Table A.
Do you have an idea to do it ?


Thanks !

2 Replies

Re: How to compare two fields from two different tables ?

Hi,

You can do like below.

Data:

Load * from table 1;

Join

Load * from Table 2;

Final:

Load *,if(Date_A > Date_B,Date_B) as New_Date_B

Resident Data;

Drop table Data;

Regards,

Kaushik Solanki

balabhaskarqlik
Honored Contributor

Re: How to compare two fields from two different tables ?

TableA:

Load Key_A_B, Date_A;

left join (TableA)

TableB:

Load Key_A_B,Date_B;

NoConcatenate

FinalTable:

Load

     Key_A_B,

     Date_A,

     If(Date_B < Date_A, Date_B) as New_Date_B

Resident TableA;


Drop Table TableA;

Community Browser