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
