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.
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.
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.
