topic Re: conditional showing of expression values in QlikView

conditional showing of expression values

Thu, 21 Jul 2016 16:22:51 GMT

Hi Community,

I have two tables of similar data, here i need to reconcile the data between two tables if the amount between the two tables does not match then i need to show that values

Input:

Tab1:

id,region,amount

101,America,2000

102,Asia,4000

103,Africa,7000

104,Europe,9000

Tab2:

temp_id,Temp_region,Temp_amount

101,America,2000

102,Asia,2000

103,Africa,7000

104,Europe,8500

output:

id,region,amount

102,Asia,2000

104,Europe,500

Re: conditional showing of expression values

sunny_talwar — Thu, 21 Jul 2016 16:30:44 GMT

May be like this:

Tab1:

LOAD *,

AutoNumber(id&region&amount) as Key;

LOAD * Inline [

id,region,amount

101,America,2000

102,Asia,4000

103,Africa,7000

104,Europe,9000

];

Tab2:

LOAD * Inline [

temp_id,Temp_region,Temp_amount

101,America,2000

102,Asia,2000

103,Africa,7000

104,Europe,8500

]

Where not Exists(Key, AutoNumber(temp_id&Temp_region&Temp_amount));

DROP Table Tab1;

Re: conditional showing of expression values

narendiran — Thu, 21 Jul 2016 16:33:00 GMT

Hi,

Hope this helps

Tab1:

id,region,amount

101,America,2000

102,Asia,4000

103,Africa,7000

104,Europe,9000

Left Join (Tab1)

temp_id,Temp_region,Temp_amount

101,America,2000

102,Asia,2000

103,Africa,7000

104,Europe,8500;

Output:

Load

id, region, amount-Temp_amount as amount

Resident Tab1

where id = temp_id;

Drop Table Tab1;

Re: conditional showing of expression values

maxgro — Thu, 21 Jul 2016 16:46:13 GMT

another solution, using join and right keep

Tab1:

LOAD * Inline [

id,region,amount

101,America,2000

102,Asia,4000

103,Africa,7000

104,Europe,9000

];

join (Tab1)

LOAD temp_id as id, Temp_region as region, Temp_amount Inline [

temp_id, Temp_region, Temp_amount

101,America,2000

102,Asia,2000

103,Africa,7000

104,Europe,8500

]

;

right Keep (Tab1)

load *, amount - Temp_amount

Resident Tab1

where amount <> Temp_amount;