Compare data and mark the common one

[geogou1973]

Hello.

I attached a qvw file that has two inline tables that have common data with different fields.

I want to show in a chart all the data that the second table has and in a new column i want to have 0 for the data that not matching with the first table and with 1 the data that matching with the first table.

How can i express this in the expression of the chart.

Thank you in advance.

[its_anandrjs]

Why not you simple join this tables and get final table

See the load script

load * Inline

[DATE_ID, STORE_ID, POS_NUMBER, TICKET_NUMBER, BARCODE_13, AMOUNT

28/5/2014, 21, 14, 201, 1234567890123, 100

28/5/2014, 21, 14, 301, 2345678901234, 200

28/5/2014, 136, 12, 101, 3456789012345, 150

28/5/2014, 136, 12, 401, 4567890123456, 300

28/5/2014, 101, 10, 501, 1234567890123, 200

28/5/2014, 101, 13, 201, 6789012345678, 150];

Inner Join

LOAD DATE as DATE_ID, STORE AS STORE_ID, POS_NBR AS POS_NUMBER, TICKET_NBR AS TICKET_NUMBER, BARCODE AS BARCODE_13;

load * Inline

[DATE, STORE, POS_NBR, TICKET_NBR, BARCODE

28/5/2014, 21, 14, 201, 1234567890123

28/5/2014, 21, 14, 301, 2345678901234

28/5/2014, 136, 12, 101, 3456789012345

28/5/2014, 136, 12, 401, 4567890123456

28/5/2014, 103, 15, 501, 9012345678901

28/5/2014, 101, 16, 101, 3456789012345];

[geogou1973]

Yes, but with which expression i will have all the data of the second table and mark them in a new column with 1 as find and with 0 as not find ?

[bbi_mba_76]

Hi,

maybe this could help.

Without joins between the fields I think you can not resolve your problem. It is like an excel file in which I have the firsts columns for the first table and others columns for the second table, if I filter DATE, I see no DATE_ID.

Set analysis cannot help because the evaluation is done once for a table, not for each row.

[geogou1973]

Hello Michele.

In the chart i want to have all the data of the second table of the data loading 6 records and the the new column i want to mark as 0 the not common that are 2 records and as 1 the other 4 that are common with the first table of the data loading.

[johanlindell]

Add a key-field that is the same if the fields match. See example.

Brgds, Johan

[geogou1973]

Yes, but maybe all the project is not so complicate. I want to show only the data of the second table with a new column that will have 0 as not match and 1 that match and your example bring and other data.

Why i have to join the two tables ? With if command i take the common ones and also i take all the data of the second table but i can not mark as 0 (not find) and 1 (find)

Is there any expression from the first qvw i have attached that i will have the results i want ?

[its_anandrjs]

Add the Key field in the table and join them and in the load make the Flag field

See the load script

Tab1:

load * Inline

[DATE_ID, STORE_ID, POS_NUMBER, TICKET_NUMBER, BARCODE_13, AMOUNT

28/5/2014, 21, 14, 201, 1234567890123, 100

28/5/2014, 21, 14, 301, 2345678901234, 200

28/5/2014, 136, 12, 101, 3456789012345, 150

28/5/2014, 136, 12, 401, 4567890123456, 300

28/5/2014, 101, 10, 501, 1234567890123, 200

28/5/2014, 101, 13, 201, 6789012345678, 150];

Tab2:

load * Inline

[DATE, STORE, POS_NBR, TICKET_NBR, BARCODE

28/5/2014, 21, 14, 201, 1234567890123

28/5/2014, 21, 14, 301, 2345678901234

28/5/2014, 136, 12, 101, 3456789012345

28/5/2014, 136, 12, 401, 4567890123456

28/5/2014, 103, 15, 501, 9012345678901

28/5/2014, 101, 16, 101, 3456789012345];

NoConcatenate

Final:

LOAD DATE_ID&STORE_ID&POS_NUMBER&TICKET_NUMBER&BARCODE_13 as Key, 'Tab1' as TableFlag,* Resident Tab1;

Join

LOAD DATE&STORE&POS_NBR&TICKET_NBR&BARCODE as Key, 'Tab2' as TableFlag,* Resident Tab2;

DROP Tables Tab1,Tab2;

Left Join

LOAD

Key,

if(Count(Key) > 1,1,0) as  Flag

Resident tmp Group By Key;

And then plot straight Table2 with dimension

Dim:- DATE, STORE, POS_NBR, TICKET_NBR, BARCODE,Flag

Exper:- 1