Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table T1 with 6 records to which I want to add 4 extra records, which are the result of a cross join.
When I run the load script, synthetic keys are created.
Someone who can help me to get the desired result without synthetic keys?
Also I'm having a hard time to understanding the behaviour of the execution of the load-script. If you can advice me articles to read, that would also be cool.
Any help is greatly appreciated.
//These values are loaded first
T1:
Load * Inline [
COL1, COL2, COL3, COL4
'A','1'
'A','2'
'A','3'
'A','4'
'A','5'
'A','6'
];
//There extra rows are added afterwards
Load * Inline [
COL1
'A'
];
join
Load * Inline [
COL2
'7'
'8'
'9'
'10'
];
join
load * inline [
COL3, COL4
];
Thank you,
Ramzi
Maybe like
//These values are loaded first
T1:
Load * Inline [
COL1, COL2, COL3, COL4
'A','1'
'A','2'
'A','3'
'A','4'
'A','5'
'A','6'
];
TMP:
//There extra rows are added afterwards
Load * Inline [
COL1
'A'
];
join
Load * Inline [
COL2
'7'
'8'
'9'
'10'
];
join
load * inline [
COL3, COL4
];
Concatenate (T1)
LOAD * RESIDENT TMP;
DROP TABLE TMP;
Maybe like
//These values are loaded first
T1:
Load * Inline [
COL1, COL2, COL3, COL4
'A','1'
'A','2'
'A','3'
'A','4'
'A','5'
'A','6'
];
TMP:
//There extra rows are added afterwards
Load * Inline [
COL1
'A'
];
join
Load * Inline [
COL2
'7'
'8'
'9'
'10'
];
join
load * inline [
COL3, COL4
];
Concatenate (T1)
LOAD * RESIDENT TMP;
DROP TABLE TMP;
After the line "//There extra rows are added afterwards" below this why you not use Join can you check this
//These values are loaded first
T1:
Load * Inline [
COL1, COL2, COL3, COL4
'A','1'
'A','2'
'A','3'
'A','4'
'A','5'
'A','6'
];
//There extra rows are added afterwards
Join
Load * Inline [
COL1
'A'
];
join
Load * Inline [
COL2
'7'
'8'
'9'
'10'
];
join
load * inline [
COL3, COL4
];
Change your script to:
//These values are loaded first
T1:
Load * Inline [
COL1, COL2, COL3, COL4
'A','1'
'A','2'
'A','3'
'A','4'
'A','5'
'A','6'
];
//There extra rows are added afterwards
T2:
Load * Inline [
COL1
'A'
];
join (T2)
Load * Inline [
COL2
'7'
'8'
'9'
'10'
];
join (T2) //but this isn't needed
load * inline [
COL3, COL4
];
concatenate (T1) load * resident T2;
drop table T2;
What do you mean with the behaviour of the script?
Qlik is linking fields from tables by the same field name(s).
There should be always only one link field between tables. If you need Information from many fields get them together (F1 & F2 & F3 as Link).
There mustn't be circulations between tables. Qlik will drop one link.
When you load tables with same fields they will be concatenated by Default.
Regards
Awesome, works. Thank you very much!
Thank you for the help. This is the result:
Thank you, works! Is very similar to Stefans solution.
I guess, because the lack of experience, I can't always explain the result of the script.
You just needed to concatenate the joined table to the first table (that's basically what you already described in your post title).
Auto-concatenation does not work if you create a table step by step (multiple tables joined), if that's what you have expected.
That was indeed what I expected. Thanks.