topic Re: Matching data from two tables in QlikView

Matching data from two tables

Wed, 17 Dec 2014 14:46:05 GMT

Hi All

I have two tables in data model and I want to eliminate unmatched records between two tables.

In below example i want to show only ABC,DCS values only and i want to exclude BCD .

how we can do it in script level,pls provide your suggestions.

table1:

Key,MAT,value

1, ABC, 1

2, BCD, 2

3, DCS ,3

Table 2 :

Key, Comp,value

1, ABC, 2

3, DCS,3

output

Key,MAT,value

1, ABC, 3

3, DCS ,6

Thanks

Re: Matching data from two tables

jerem1234 — Wed, 17 Dec 2014 15:09:47 GMT

Hi SwathiRaj P,

You will need to do joins in the script. I created an example from your data, please find attached dashboard. I used the script:

Table1:

Load * Inline [

Key,MAT,value

1,ABC,1

2,BCD,2

3,DCS,3

];

Inner Join(Table1)

Table2:

Load Key,Comp as MAT, value2 Inline [

Key, Comp,value2

1, ABC, 2

3, DCS,3

];

Final:

NoConcatenate Load Key, MAT, value+value2 as Value

Resident Table1;

Drop Table Table1;

Hope this helps!

Re: Matching data from two tables

its_anandrjs — Wed, 17 Dec 2014 15:38:25 GMT

Hi,

Another way of the same for doing is

Tmp:
LOAD Key, MAT as Comp, value as Value1;
LOAD * INLINE [
Key, MAT, value
1, ABC, 1
2, BCD, 2
3, DCS, 3
];
Inner Join

LOAD Key, Comp, value;
LOAD * INLINE [
Key, Comp, value
1, ABC, 2
3, DCS, 3
];

LOAD
Key, Comp,
Sum(value)+Sum(Value1) as Comm
Resident Tmp
Group By Key, Comp;
DROP Table Tmp;

Regards

Anand