Qlik script log shows TableName-2 during LEFT JOIN

[sogloqlik]

Hi,

I am working with Qlik SaaS load script and I have a question about the reload log behavior during a LEFT JOIN.

I have a script that creates an invoice-agreement bridge table using IntervalMatch. The result is correct, and in the Data Model Viewer I do not see any duplicated table. However, during reload, the log shows a table name with a -2 suffix when joining the fact data into the interval match result.

Relevant code:

TempInvoiceAgreementMatch:
INTERVALMATCH (InvoiceDateNum, Customer)
LOAD DISTINCT
    ValidFromNum,
    ValidToNum,
    Customer
RESIDENT TempAgreementsForMatch;

LEFT JOIN (TempInvoiceAgreementMatch)
LOAD DISTINCT
    ValidFromNum,
    ValidToNum,
    Customer,
    AgreementNumber,
    AgreementFixedAmount
RESIDENT TempAgreementsForMatch;

DROP TABLE TempAgreementsForMatch;

LEFT JOIN (TempInvoiceAgreementMatch)
LOAD DISTINCT
    InvoiceDateNum,
    Customer,
    [Doc_VBELN],
    InvoiceMonthNum
RESIDENT TempFactForMatch;

DROP TABLE TempFactForMatch;

The reload log shows something like this:

TempInvoiceAgreementMatch << TempAgreementsForMatch
Lines fetched: 420,516

TempInvoiceAgreementMatch-2 << TempFactForMatch
Lines fetched: 4,632,847

MapFirstAgreementMonth << TempInvoiceAgreementMatch
Lines fetched: 943

AllocationBase_Temp1 << TempInvoiceAgreementMatch
Lines fetched: 1,344

InvoiceAgreementBridge << TempInvoiceAgreementMatch
Lines fetched: 107,713

The final result is correct, and TempInvoiceAgreementMatch-2 does not appear in the final data model.

My question:

Is TempInvoiceAgreementMatch-2 in the reload log just an internal temporary/staging table name that Qlik creates while processing the LEFT JOIN, or does it indicate that Qlik is actually creating a separate table during the join?

Thanks.

[marksouzacosta · Accepted solution]

Hi @sogloqlik,

Since you are not assigning names to the tables during the LEFT JOIN, Qlik is automatically creating that name for you - this is not a problem and actually a common practice.

To avoid this, just add a name to every single table loaded. Example:

TempInvoiceAgreementMatch:
INTERVALMATCH (InvoiceDateNum, Customer)
LOAD DISTINCT
    ValidFromNum,
    ValidToNum,
    Customer
RESIDENT TempAgreementsForMatch;

LEFT JOIN (TempInvoiceAgreementMatch)
MyNamedTable1:
LOAD DISTINCT
    ValidFromNum,
    ValidToNum,
    Customer,
    AgreementNumber,
    AgreementFixedAmount
RESIDENT TempAgreementsForMatch;

DROP TABLE TempAgreementsForMatch;

LEFT JOIN (TempInvoiceAgreementMatch)
MyNamedTable2:
LOAD DISTINCT
    InvoiceDateNum,
    Customer,
    [Doc_VBELN],
    InvoiceMonthNum
RESIDENT TempFactForMatch;

DROP TABLE TempFactForMatch;

Regards,

Mark Costa

[marcus_sommer · Accepted solution]

I think it's an internal counter to an intermediate step - initializing the load and loading the data at first temporary before performing the join-operation.

There are probably several reasons to keep loads separate which is some aspects further visible within the logs. This may be the applied DISTINCT statement which worked on both sides of a merge-approach or the use of the ERRORMODE or any partial load-methods and assuming some more.

If everything worked like expected you don't need to worry about it.

[sogloqlik]

Thank you both, that explanation helps.

I have one follow-up question.

If I perform the same LEFT JOIN directly from a QVD source, I do not see the same TableName-2 behavior in the reload log.

For example, when the join source is loaded from a QVD, the log looks normal. But when the join source is a RESIDENT table, the reload log shows something like:

TargetTable-2 << ResidentSourceTable

Is the difference only related to how Qlik writes the reload log when the join source is a resident in-memory table?

In other words, does TargetTable-2 simply represent an internal/intermediate load step used during join processing, while loading directly from QVD does not expose the same intermediate name in the log?

Just trying to confirm whether this is only a logging/representation difference and not a difference in the final data model behavior.

[marcus_sommer]

I would assume that the kind of source (resident vs qvd) is irrelevant.

More relevant might be if the load contains any transformations or not respectively if the load is optimized or not. Further impact may be caused from the order of the load-statements respectively are any different load/table-statements inbetween them - other loads, the drop statement, ...

[sogloqlik]

Interesting.

The reason I asked is that I usually prefer working with QVDs rather than resident tables, mainly for maintenance purposes. After writing many joins this way, I do not recall encountering this issue before.

However, if this is only a logging issue and does not affect the actual data load or results, I can live with that.

Tnx