topic Re: Non-unique fields causing duplicate records when tables joined. in QlikView

Non-unique fields causing duplicate records when tables joined.

Tue, 21 May 2013 21:15:23 GMT

I have been given a script with the following:

CallTable:
SQL SELECT
     unique_id,
     start_time,
     region_key,
     result
FROM $(DB_NAME).$(vTableName);
join (CallTable)
Load
start_time,
maketime(hour(start_time),minute(start_time),second(start_time)) as [Call Start Time]
Resident
CallTable;

The resulting CallTable then gets stored in a QVD and later loaded by another script for reporting purposes.

This works fine except in certain cases where, I believe, two records have the same start_time value. When this happens, then we end up with duplicate records in the table/QVD which we can see by creating a list box for unique_id with the frequency option enabled. How can I modify this script to prevent these duplicate records? Since we're working with a fairly large dataset an efficient solution is preferable.

Thanks

Steve

Re: Non-unique fields causing duplicate records when tables joined.

swuehl — Tue, 21 May 2013 21:21:11 GMT

Maybe I misunderstood what you are trying to achieve, but why not try a preceding load:

CallTable:
LOAD *,
      maketime(hour(start_time),minute(start_time),second(start_time)) as [Call Start Time];
SQL SELECT      
     unique_id,
     start_time,
     region_key,
     result 
FROM $(DB_NAME).$(vTableName);

Re: Non-unique fields causing duplicate records when tables joined.

Wed, 22 May 2013 13:44:21 GMT

Perfect, thanks.