topic How to compare 2 non identical tables in QlikView

Help:How to compare 2 non identical tables

Sun, 06 Mar 2011 17:47:34 GMT

Hi,

I developed a qlick view report with logic like below

for (tableA.row=0 to tableA.max(row)

If tableA.(a>0 and b>0,c>0) load tableB.*,tableA.a,tableA.b,tableA.c from resident tableB where tableB.rank=0

If tableA.(b>0 and c>0,d>0) load tableB.*,tableA.a,tableA.b,tableA.c from resident tableB where tableB.rank=1

If tableA.(c>0 and d>0,e>0) load tableB.*,tableA.a,tableA.b,tableA.c from resident tableB where tableB.rank=2

If tableA.(d>0 and e>0,f>0) load tableB.*,tableA.a,tableA.b,tableA.c from resident tableB where tableB.rank=3

tableA.next row

end loop

I had to use two For Loops( For each row in tableA check all rows in table when matched Load/Display ( like Sorting))

It works fine but I think is not an optimum solution as it takes long time for huge tables (with Lots of data).

I want to know if there is any function or feature that can compare two sets of data in QlikView like in my case which is more optimal .

i am new to QlikView reports any advice or suggestions in this will help a lot

Thanks in advance

How to compare 2 non identical tables

prieper — Sun, 06 Mar 2011 18:33:46 GMT

Would be useful to have some examples.
On first glance would suggest to construct a loop, in which you count your conditions and the access the tableB with the PEEK-command:
Quote

peek ( fieldname [ , row [ , tablename ] ] )

Returns the contents of the fieldname in the record specified by row in the input table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed.

Tablename is a table label without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.

Unquote

HTH
Peter

How to compare 2 non identical tables

Sun, 06 Mar 2011 18:51:40 GMT

Thanks Peter,

Here is sample of what I have,It take very long, as the two table are huge.

RESULT:
let vRowCount = NoOfRows('Source');
for i=0 to $(vRowCount)-1

LET A = peek('A',$(i),'Source');
LET B= peek('B',$(i),'Source');
LET C = peek('C',$(i),'Source');

LET vRowCount_2 = NoOfRows('TARGET');
for sf=0 to $(vRowCount_2)-1
LET A_F = peek('A',$(sf),'TARGET');
LET B_F = peek('B',$(sf),'TARGET');
LET C_F = peek('C',$(sf),'TARGET');
LET RANK_F = peek('RANK',$(sf),'TARGET');

if(A=A_F AND B=B_F AND C='ALL' AND RANK_F=0 ) THEN
load '$(A)' ,B_F, A_F
RESIDENT TARGET WHERE A_F='$(A)' AND B_F='$(B)';
EXIT FOR
end if
if(A=B_F AND B=C_F AND RANK_F=1 ) THEN
load '$(A)' ,B_F, A_F,C_F
RESIDENT TARGET WHERE C_F='$(A)' AND B_F='$(B)'EXIT FOR
end if
NEXT sf
next i

topic How to compare 2 non identical tables in QlikView

Help:How to compare 2 non identical tables

How to compare 2 non identical tables

peek( fieldname [ , row [ , tablename ] ] )

How to compare 2 non identical tables

peek ( fieldname [ , row [ , tablename ] ] )