topic Re: Matching Records in One Table in QlikView

Matching Records in One Table

nwilliams — Thu, 24 Jul 2014 21:13:49 GMT

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.

Re: Matching Records in One Table

Thu, 24 Jul 2014 21:33:25 GMT

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

Re: Re: Matching Records in One Table

nwilliams — Thu, 24 Jul 2014 21:49:48 GMT

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

Re: Matching Records in One Table

Anonymous — Thu, 24 Jul 2014 22:37:31 GMT

A little bit different solution but may work..

Re: Matching Records in One Table

nwilliams — Fri, 25 Jul 2014 07:57:46 GMT

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.