Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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];
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 ?
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.
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.
Add a key-field that is the same if the fields match. See example.
Brgds, Johan
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 ?
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