Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
david_maurice
Contributor III
Contributor III

Synthetic Table creation overriding user table creation?

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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 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:
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

View solution in original post

2 Replies
swuehl
MVP
MVP

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 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:
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

View solution in original post

david_maurice
Contributor III
Contributor III
Author

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!