Getting Synthetic key when using loop in one table as I'm fetching Last Quarter data and Current Quarter data.

[Prakash004]

Hi Team,

I'm getting synthetic key when we implemented the JOIN condition in the For Loop in the same table . As we are fetching Last Quarter data and Current Quarter data.

I've included the code for reference. Requesting your help please.

[Employee]:
Let i=0;

If ('$(var_prev_qtr_end_date0)' = '$(var_prev_qtr_end_date1)') then 
FOR i = 1 to 2
set qtr_end = $(var_prev_qtr_end_date$(i));

LOAD
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"Country",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER" ;
SQL SELECT
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER",
FROM PROJECT"('PLACEHOLDER' = ('$$IP_DATA_AS_ON$$',
'$(qtr_end)')) ;

left Join ([Employee])

LOAD
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"Country",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER" ;
SQL SELECT
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER",
FROM PROJECT"('PLACEHOLDER' = ('$$IP_DATA_AS_ON$$',
'$(qtr_end)'));

left Join ([Employee])

LOAD
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"Country",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER" ;
SQL SELECT
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER",
FROM PROJECT"('PLACEHOLDER' = ('$$IP_DATA_AS_ON$$',
'$(qtr_end)'));

Next ;

End If

If ('$(var_prev_qtr_end_date0)' <> '$(var_prev_qtr_end_date1)') then 
FOR i = 0 to 1
set qtr_end = $(var_prev_qtr_end_date$(i));

LOAD
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"Country",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER" ;
SQL SELECT
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER",
FROM PROJECT"('PLACEHOLDER' = ('$$IP_DATA_AS_ON$$',
'$(qtr_end)'));

left Join ([Employee])

LOAD
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"Country",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER" ;
SQL SELECT
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER",
FROM PROJECT"('PLACEHOLDER' = ('$$IP_DATA_AS_ON$$',
'$(qtr_end)'));

Left Join ([Employee])

LOAD
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"Country",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER" ;
SQL SELECT
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER",
FROM PROJECT"('PLACEHOLDER' = ('$$IP_DATA_AS_ON$$',
'$(qtr_end)'));

left Join ([Employee])
LOAD
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"Country",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER" ;
SQL SELECT
"PROJECT_NUMBER",
"CUSTOMER_NAME",
"SERVICE_LINE",
"PROJECT",
"PRODUCT",
"YEAR",
"QUARTER",
FROM PROJECT"('PLACEHOLDER' = ('$$IP_DATA_AS_ON$$',
'$(qtr_end)'));

Next ;

End If

[WildmoserGeorg]

try this:

A) In the loop, first create a temporary table on which the left join is performed

B) Concatenate the temporary table into your final table

C) Drop the temporary table for the next loop

script example:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='de-DE';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan.;Feb.;März;Apr.;Mai;Juni;Juli;Aug.;Sep.;Okt.;Nov.;Dez.';
SET LongMonthNames='Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember';
SET DayNames='Mo.;Di.;Mi.;Do.;Fr.;Sa.;So.';
SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

a:
NoConcatenate
load * inline [
id
a1
a2
a3
];

b:
NoConcatenate
load * inline [
id
b1
b2
b3
];

aa:
NoConcatenate
load * inline [
id,val
a1,a1val
a3,a3val
];

bb:
NoConcatenate
load * inline [
id,val
b2,b2val
];

tables:
NoConcatenate
load * inline [
sourcetable,valuetable
a,aa
b,bb
];

let vNumRows = NoOfRows('tables');

NewTable_finish:
NoConcatenate
load * inline [id,val];

for i=1 to $(vNumRows)

Let vSourceTable = FieldValue('sourcetable',$(i));
Let vValueTable = FieldValue('valuetable',$(i));

trace '$(vSourceTable)';
trace '$(vValueTable)';

NewTable_tmp:
NoConcatenate
Load * resident $(vSourceTable);

left join (NewTable_tmp)
Load id, val resident $(vValueTable);

Concatenate(NewTable_finish)
Load * resident NewTable_tmp;

drop table NewTable_tmp;
drop table '$(vSourceTable)';
drop table '$(vValueTable)';
//exit Script;

Next;

result:

[marcus_sommer]

Don't join the table else concatenate them (union in sql) - it's much easier and more expedient.

[Prakash004]

concatenate will just append the values from the bottom table and it'll give duplicate records for all the dimensions.

[marcus_sommer]

A concatenation will increase the number of records - but only within the data-table which contained only bit-stuffed index-values and not within the system-tables because Qlik stores there only distinct field-values. The number of records within a data-model is at itself not essential to decide the quality of the data-model design or to the performance. The bigger the dataset and the more redundant data exists the bigger are the benefits from this column-oriented storing-system.

Beside this is joining tables a quite risky measure in regard to effect the number of records respectively needs extra efforts to check the relationship of the keys and handling unsuitable ones. Further is joining rather slow compared to concatenations and very important you could never join respectively associate records with missing key-values which happens quite often. Of course this could be handled by appropriate checking and populating of the missing data but it will increase the complexity quite heavily. Further joining-approaches are creating crosstable-structures which have serious disadvantages to the usability. Therefore I suggest to consider the concatenation as a valid alternatively to a joining-logic.