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

[#1]

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

[swuehl · Accepted solution]

If I understood correctly, you could use something like

INPUT:

LOAD * INLINE [

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:

NOCONCATENATE LOAD Numbers,

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.

Please see attached.

Stefan

[#3]

yes, its the good answer, thank you, its help me