Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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....
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.
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;
You get tableb as well, would you provide full script
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 *;
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....
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);
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