Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

thnkgreen
Contributor

Tables will not CONCATENATE as expected

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Tables will not CONCATENATE as expected

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
7 Replies
p_verkooijen
Valued Contributor

Re: Tables will not CONCATENATE as expected

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

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

You get tableb as well, would you provide full script

Life is so rich, and we need to respect to the life !!!
neelamsaroha157
Valued Contributor II

Re: Tables will not CONCATENATE as expected

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 *;

MVP
MVP

Re: Tables will not CONCATENATE as expected

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
p_verkooijen
Valued Contributor

Re: Tables will not CONCATENATE as expected

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);

thnkgreen
Contributor

Re: Tables will not CONCATENATE as expected

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

Community Browser