topic Re: Tables will not CONCATENATE as expected in QlikView

Tables will not CONCATENATE as expected

Anonymous — Tue, 20 Sep 2016 13:26:35 GMT

Hello my QlikView friends. I have an issue with table concatenation that has me stumped. Basically, I am loading one table from a qvd, and then another table by using a SQL Select statement. My expectation is that the table created with a SQL SELECT would merge all fields with TABLE_A, and that my result set would be several fields beginning with TABLE_A since they are to be qualified. Instead, the fields from the second table are appearing as a second set of fields, with an arbitrary name taken from within the SQL SELECT portion as their qualifying header. I hope this makes sense. I have tried to make sure that the exact same field names are being used for both tables and that there are the same number of fields so that they should blend perfectly into one table. I am not sure what is happening? Thanks. Here is my simple syntax:

QUALIFY *;

TABLE_A:
LOAD *
FROM Path (qvd);

CONCATENATE

LOAD *;

SQL SELECT (code);

UNQUALIFY *;

I expect TABLE_A.first name

TABLE_A.last name

TABLE_A.phone number

========================================

Instead I get TABLE_A.first name

TABLE_A.last name

TABLE_A.phone number

TABLE_B.first name

TABLE_B.last name

TABLE_B.phone number

Re: Tables will not CONCATENATE as expected

p_verkooijen — Tue, 20 Sep 2016 13:31:46 GMT

whoops almost read the question in total so the first reply is somewhat short

Use:

CONCATENATE (TABLE_A)

LOAD *;

SQL SELECT (code);

added reply:

However it is best to provide the table to concatenate with.

Because the concatenate happens within the Qualify * and it is concatenated to TABLE_A this is their prefix.

Re: Tables will not CONCATENATE as expected

rubenmarin — Tue, 20 Sep 2016 13:36:52 GMT

Hi Robert, the data is concatenated in table A, table B is not created, if you want that output first load the tables and the do the concatenation:

Qualify *;

TableA:

LOAD ....

TableB:

NoConcatenate LOAD ...

Concatenate (TableA) LOAD* resident TableB;

DROP TableB;

Re: Tables will not CONCATENATE as expected

Anil_Babu_Samineni — Tue, 20 Sep 2016 13:38:57 GMT

You get tableb as well, would you provide full script

Re: Tables will not CONCATENATE as expected

neelamsaroha157 — Tue, 20 Sep 2016 13:39:29 GMT

May be you can try this

QUALIFY *;

TABLE_A:
LOAD *
FROM Path (qvd);

UNQUALIFY *;

RENAME Table TABLE_A to TABLE_X;

CONCATENATE(TABLE_X)

QUALIFY *;

TABLE_A:

LOAD *;

SQL SELECT (code);

UNQUALIFY *;

Re: Tables will not CONCATENATE as expected

jonathandienst — Tue, 20 Sep 2016 13:45:33 GMT

The Qualify state means that the second load gets a different qualification before the concatenate takes place. You may want to take control of the prefix in the second load rather than relying on Qualify. Something like:

QUALIFY *;

TABLE_A:

LOAD *

FROM Path (qvd);

UNQUALIFY *;

CONCATENATE

LOAD [first name] as [TABLE_A.first name],

[last name] as [TABLE_A.last name],

[phone number] as [TABLE_A.phone number]

;

SQL SELECT [first name], ....

But do you really need to qualify this load....

Re: Tables will not CONCATENATE as expected

p_verkooijen — Tue, 20 Sep 2016 13:45:34 GMT

You should create a string of fields that allready exists in TABLE_A, remove the prefix and use these in a UNQUALIFY.

Something like:

QUALIFY *;

TABLE_A:

LOAD *

FROM Path (qvd);

LET vNoofFields = NoOfFields('TABLE_A');

For i = 1 to vNoofFields

Let vFieldNames = REPLACE(IF(i=1, FieldName(i, 'TABLE_A'), $(vFieldNames)& ', ' & FieldName(i, 'TABLE_A')), 'TABLE_A.', '');

Next i

UNQUALIFY vFieldNames;

CONCATENATE (TABLE_A)

LOAD *;

SQL SELECT (code);

Re: Tables will not CONCATENATE as expected

Anonymous — Tue, 20 Sep 2016 14:09:20 GMT

Thanks to everyone for helping out. This answer worked. I do need to qualify the first table because there are many other tables in this qvw. Thanks again