Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ramzi-manoubi
Partner - Creator
Partner - Creator

Concatenate a joined table, without synthetic keys

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

8 Replies
swuehl
MVP
MVP

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;

its_anandrjs

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

];

martinpohl
Partner - Master
Partner - Master

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

ramzi-manoubi
Partner - Creator
Partner - Creator
Author

Awesome, works. Thank you very much!

ramzi-manoubi
Partner - Creator
Partner - Creator
Author

Thank you for the help. This is the result:

ramzi-manoubi
Partner - Creator
Partner - Creator
Author

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.

swuehl
MVP
MVP

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.

ramzi-manoubi
Partner - Creator
Partner - Creator
Author

That was indeed what I expected. Thanks.