Matching Records in One Table

[nwilliams]

Evening,

I'm loading data from 3 QVD's into one table.  I'm trying to match records that have the same email address and value, but different version number.  Ie

Email               Value               Version               Output

john@com     12345               8.5                        DiffVerAva

john@com     12345               8.8                        DiffVerAva

jill@com          23456              8.5                         NoOtherVer

jill@com          34567               8.8                         NoOtherVer

I have no idea how you would do this, or even if it possible doing it in one table.  I can put all the data with Version 8.8 into a different table if needed.

Any thoughts welcome.

[#2]

You can create a Key(say Email) and all the three tables would hence be connected through common email ids.

You can also use Applymap or even Joins.

For further clarification please attach some sample data and the desired output that you are looking for.

Thanks

[nwilliams]

Hi,

Below is the script I'm using.  I'm concatenating 3 QVD's into one table, the creating a left Join to identify duplicate records.

//Connected Backup 8.5 - UK Data Load
LOAD AllActiveAccounts.account,
    AllActiveAccounts.agentversion,,
    AllActiveAccounts.backupdate,
    if(AllActiveAccounts.Email='','None', AllActiveAccounts.Email) AS AllActiveAccounts.Email,
    if(AllActiveAccounts.Email='','None', AllActiveAccounts.Email) AS lnk_user_email,
    AllActiveAccounts.startdate,
    AllActiveAccounts.value,
    AllActiveAccounts.value  AS lnk_asset_name,
    'UK' AS AllActiveAccounts.c_Country,
    '8.5' AS AllActiveAccounts.c_CBVersion,
    Num((Date(Today()))-AllActiveAccounts.backupdate,'#.') AS AllActiveAccounts.c_DaysSinceBackup
FROM

(qvd);

//Connected Backup 8.8 - UK Data Load
Concatenate
LOAD AllActiveAccounts.AssetName,
    AllActiveAccounts.account,
    AllActiveAccounts.agentversion,
    AllActiveAccounts.backupdate,
    AllActiveAccounts.department,
    if(AllActiveAccounts.Email='','None', AllActiveAccounts.Email) AS AllActiveAccounts.Email,
    if(AllActiveAccounts.Email='','None', AllActiveAccounts.Email) AS lnk_user_email,
    AllActiveAccounts.firstname,
    AllActiveAccounts.lastname,
    AllActiveAccounts.startdate,
    AllActiveAccounts.value,
    AllActiveAccounts.value AS lnk_asset_name,
    'UK' AS AllActiveAccounts.c_Country,
    '8.8' AS AllActiveAccounts.c_CBVersion,
    Num((Date(Today()))-AllActiveAccounts.backupdate,'#.') AS AllActiveAccounts.c_DaysSinceBackup
FROM

(qvd);

//Duplicate Identification
Left Join (AllActiveAccounts)
LOAD
AllActiveAccounts.Email,
AllActiveAccounts.value,
AllActiveAccounts.c_CBVersion,
count(AllActiveAccounts.account) AS AllActiveAccounts.c_DuplicateAccount
Resident AllActiveAccounts
group by
    AllActiveAccounts.Email,
    AllActiveAccounts.c_CBVersion,
    AllActiveAccounts.value;

I have attached an xls with a few rows of sample data. Column H shows if there is a similar record, but on a different version.  The similar record is based on the email address, and value being the same.

Hope this makes sense

[Anonymous]

A little bit different solution but may work..

[nwilliams]

Hi,

I have worked round this by loading the 3rd QVD into a different table, then doing a "if" expression in a chart to show me the records.  Not ideal, but workable.