Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ramzi-manoubi
Contributor

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
MVP
MVP

Re: Concatenate a joined table, without synthetic keys

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;

8 Replies
MVP
MVP

Re: Concatenate a joined table, without synthetic keys

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;

Re: Concatenate a joined table, without synthetic keys

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
Valued Contributor II

Re: Concatenate a joined table, without synthetic keys

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
Contributor

Re: Concatenate a joined table, without synthetic keys

Awesome, works. Thank you very much!

ramzi-manoubi
Contributor

Re: Concatenate a joined table, without synthetic keys

Thank you for the help. This is the result:

ramzi-manoubi
Contributor

Re: Concatenate a joined table, without synthetic keys

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.

MVP
MVP

Re: Concatenate a joined table, without synthetic keys

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
Contributor

Re: Concatenate a joined table, without synthetic keys

That was indeed what I expected. Thanks.

Community Browser