Solved: Prevent automatic concatenation after binary load - Qlik Community

Anonymous · ‎2011-04-22

<body>I am loading a table through a binary load. This table is to be merged with a second table with the same field names but only after the second table has undergone some mapping and converting. <quote></quote> <code> BINARY 'C:\table1.qvw'; table2: NoConcatenate LOAD [FIELD1], [FIELD2], ApplyMap( 'mapX', [FIELD3], '' ) FROM 'c:\source2.qvd' (qvd); JOIN (table1) LOAD * resident table2; </code> No matter what I do, 'table1' will get merged with 'table2'. The NoConcatenate addition does not seem to have an effect on the table loaded through the BINARY command. I have tried a QUALIFY command in the table1.qvw, but the UNQUALIFY has no effect on the table after the BINARY load. How to prevent the automatic concatenation in this case?</body>

Report Inappropriate Content · ‎2011-04-22

Hi Olaf,

you do have two tables. Take a look at the table viewer (ctrl T or smart icon). There you can find TWO tables linked together by a so called sync-table (in this case "$Sync1"). The corresponding message can be seen in the load output window, it is like "$Syn 1 = COUNTRY+SALES" This is to say QV links automatically table1 and table2 together via same fieldnames. This is very a very good and convenient feature, if you familar with it. But if you do not know how to use this feature it can have surprising affects.

When you look at my attached app, the qualify() - statement works fine. Proof it using the table viewer again or during creating a new table box. See the pull down list "Show fields from Table".

So I think you need to know what is the key to link the two tables together. My first guess it is country and area. If so, rename xflag to area (this seems to fit to area) and load again. Or add ID to the first table, if this is your common key-field. Or use qualify to get two independent tables.

OK, I suppose this is a lot of home work, don't hesitate to ask for details if necessary.

RR

View solution in original post

Report Inappropriate Content · ‎2011-04-22

Hello,

I was trying to rebuild your example as you described it, but in vain. I trid with/without "noconcat", with(out) mapping and so on. My table2 will not be appended to table1 when I use the NoConcatenate like you described it.

I assume table1 and table2 have same fieldnames (fieldname [FIELD3] !), So can you send a little exam to see what is wrong ?

Regards, Roland

Anonymous · ‎2011-04-22

Partly they have the same fields. Table1 has one field that table2 doesn't. Table2 has two fields that table1 doesn't have. They share two fields. The names of the two shared fields are the same in both tables.

I am attaching an example (in two stages, because you can only upload one file per post/reply).

Table2.qvw performs a binary load on table1, then loads it's own table with the addition NoConcat. The result however is one table with lines from both tables.

Anonymous · ‎2011-04-22

Part 2 of 2.

Report Inappropriate Content · ‎2011-04-22

Hi Olaf,

you do have two tables. Take a look at the table viewer (ctrl T or smart icon). There you can find TWO tables linked together by a so called sync-table (in this case "$Sync1"). The corresponding message can be seen in the load output window, it is like "$Syn 1 = COUNTRY+SALES" This is to say QV links automatically table1 and table2 together via same fieldnames. This is very a very good and convenient feature, if you familar with it. But if you do not know how to use this feature it can have surprising affects.

When you look at my attached app, the qualify() - statement works fine. Proof it using the table viewer again or during creating a new table box. See the pull down list "Show fields from Table".

So I think you need to know what is the key to link the two tables together. My first guess it is country and area. If so, rename xflag to area (this seems to fit to area) and load again. Or add ID to the first table, if this is your common key-field. Or use qualify to get two independent tables.

OK, I suppose this is a lot of home work, don't hesitate to ask for details if necessary.

RR

Anonymous · ‎2011-04-27

Your explanation made me rethink my synthetic keys. Thanks.

PS I could not verify your qualify-solution because there seems to be missing one part of your application.

Report Inappropriate Content · ‎2011-04-27

Hi Olaf again,

when you look into the script you will find the qualify-statement before the load-table2-statement:

QUALIFY *;
table2:
load * Inline [
ID, COUNTRY, AREA, SALES
1, NL, A, 10
2, FR, A, 30
3, FR, B, 25
];

With this version of the script table1and table2 won't be linked together, because the fieldnames of table2 are qualified eg. : table2.ID and table2.COUNTRY. As an result the tables are NOT linked together.

But if you comment the qualify()-statement and reload again, you will see the link (again).

OK?

RR