topic Synthetic Table creation overriding user table creation? in Connectivity & Data Prep

Synthetic Table creation overriding user table creation?

Anonymous — Mon, 01 Feb 2016 23:13:54 GMT

Hi, just a quick question here about the desired behaviour when a Synthetic Table is created.

My scenario is this - imagine "customers" which may have multiple names for a given month over time. We want a table with one name per customer for the most recent month.

Now, this can be done easily inside a DBMS but doing it entirely in the Qlik data load step showed some funny behaviour.

Taking my test data:

testdata:

load * inline

[

mth, CustomerID, CustomerName

4, 1, 'fred'

4, 1, 'tom'

3, 1, 'fred'

2, 1, 'fred'

4, 2, 'phil'

4, 3, 'jack'

3, 4, 'sam'

]

;

I create a table containing one name per month and customer, then the most recent month by customer. These tables are then inner-joined to get the most recent month and name by customer.

If the two intermediate tables have exactly the same column names, in the data load step Qlik creates a synthetic key table, part of which is our desired output. The table even though it is specifically called on to be created in the script is not created!

However, if the column names are not the same and no synthetic key is created, the table is created as per the script!

Using the test data above, this replicates the problem:

//Get one entry per customer per month
CustomerMaster:
LOAD
mth as ReferenceMonth,
CustomerID,
MAXSTRING(CustomerName) as CustomerName2
resident
testdata
GROUP BY mth, CustomerID
;

//CREATE MOST RECENT ENTRY
CustomerRecent:
LOAD
CustomerID,
MAX(ReferenceMonth) as ReferenceMonth //If this is called "ReferenceMonth", the table creation below fails. If it has another name, the table is created as per the script
resident
CustomerMaster
GROUP BY CustomerID
;

//CREATE LIST OF MOST RECENT NAMES
CustomerNames:
load
ReferenceMonth as ReferenceMonth,
    CustomerID
resident CustomerRecent;
INNER JOIN
load
ReferenceMonth,
    CustomerID,
    CustomerName2
resident CustomerMaster;
;

//CLEAN UP UN-NEEDED TABLES
drop table CustomerMaster, CustomerRecent

Re: Synthetic Table creation overriding user table creation?

swuehl — Tue, 02 Feb 2016 00:01:08 GMT

I think that's because QV will auto-concatenate tables with same number and name of fields.

Try

Using the test data above, this replicates the problem:

//Get one entry per customer per month
CustomerMaster:
LOAD
mth as ReferenceMonth,
CustomerID,
MAXSTRING(CustomerName) as CustomerName2
resident
testdata
GROUP BY mth, CustomerID
;

/CREATE LIST OF MOST RECENT NAMES
CustomerNames:
NOCONCATENATE load
ReferenceMonth as ReferenceMonth,
    CustomerID
resident CustomerRecent;
INNER JOIN
load
ReferenceMonth,
    CustomerID,
    CustomerName2
resident CustomerMaster;
;

//CLEAN UP UN-NEEDED TABLES
drop table CustomerMaster, CustomerRecent

Re: Synthetic Table creation overriding user table creation?

Anonymous — Tue, 02 Feb 2016 00:03:30 GMT

Seems like odd behaviour if I've specifically got in the load script to create a new table with a different name and contents, but I imagine this is in the tiny percent of cases regardless.

Thanks!