2 Replies Latest reply: Feb 6, 2012 11:50 AM by Alexandre POTTIER

# how to search same value in different columns and differents lign row

Hi Guys,

I want use Qlikview for bank reconciliation, i explain :

I have two columns, Column A and Column B

In my columns, i have many numbers. The sum of numbers in column A must be equal of the sum of numbers in column B.

I can have several times the same number in one column, but carreful, number in column A is not everytime at the same row in column B.

I need Qlikview search the number to column A in all of the column B, and if the occurence of one number in column A is superior or inferior of the occurence in column B, Qlikview do something (do a color, or give the number, no matter)

I give a example (fake numbers of course) :

 Colonne A Colonne B 10 30 20 25 30 10 10 45 30 10 60 65 70 75 10 85 90 10 100 10

You see for example, the number 10 have 3 occurences in A, 4 occurence in B. I need Qlikview say to me the number 10 have one occurence in too in the B column.

Can you help me ?

The same in excel file.

Thanks

• ###### Re: how to search same value in different columns and differents lign row

If I understood correctly, you could use something like

INPUT:

Colonne A,    Colonne B

10,    30

20,    25

30,    10

10,    45

30,    10

60,    65

70,    75

10,    85

90,    10

100, 10

];

TMPRESULT:

LOAD [Colonne A] as Numbers, count([Colonne A]) as ACount resident INPUT group by [Colonne A];

outer join LOAD [Colonne B] as Numbers, count([Colonne B]) as BCount resident INPUT group by [Colonne B];

RESULT:

if(IsNull(ACount),0,ACount) as ACount,

if(IsNull(BCount),0,BCount) as BCount

resident TMPRESULT;

drop table TMPRESULT;

Then just create a straight chart with dimension Numbers and values ACount and BCount and calculate the Delta from BCount-ACount. Use Visual Cues to color the difference as you like.