topic Function Concatenate and Store in QlikView

Function Concatenate and Store

pgalvezt — Fri, 06 May 2011 15:51:09 GMT

Hello, first of all congratulations for this update, really is amazing the visualization of this page.

Recently I fixed a problem with syntethics keys witn the "Concatenate "Function. Finally Qlikview only shows one single table . But now my problem is that I incluided into the script the Function "Store" and to do qvd files for incremental Charges. And Qlikview Shows a massage: Qlikview doesn't founfd the table. If I left under comment the concateate function the Script Works. The problem is Both Concatenate and Store working together they thrown this error.

I hope you guys can help me.

Best Regards...

Function Concatenate and Store

Fri, 06 May 2011 15:55:21 GMT

Could you upload a script example?

Function Concatenate and Store

pgalvezt — Fri, 06 May 2011 16:08:41 GMT

I have:

Table_A:

LOAD

value 1,

value 2,

value 3

From

;

CONCATENATE(Table_A) //Works Perfectly alone. Join Table A and table B

Table_B:

value 1,

value 2,

value 3

From

;

Store Table_B Into QVD\Table_B.qvd; //Problem

//Store Table_B Into QVD\Table_B.qvd; //No Problem

CONCATENATE(Table_A) //Works Perfectly alone. Join Table A and table C

Table_C:

value 1,

value 2,

value 3

From

;

Store Table_C Into QVD\Table_C.qvd;//Problem

//Store Table_C Into QVD\Table_C.qvd;//No Problem

But if I add Store function Thrown the error table.

Function Concatenate and Store

erichshiino — Fri, 06 May 2011 16:13:33 GMT

Hi,

When you use concanate, the name you give to the table you are concatenating (the second one) is just ignored.

Then at:

Table_A: //you create this one

LOAD

value 1,

value 2,

value 3

From

;

CONCATENATE(Table_A) //Works Perfectly alone. Join Table A and table B

Table_B: //QlikView will ignore this - only Table A exists

value 1,

value 2,

value 3

From

;

If you try Store Table B you get an error because it does not exist.

Use just Store Table A ...

Function Concatenate and Store

Fri, 06 May 2011 16:19:10 GMT

At first glance I can see you don't have the 'LOAD' string in the last two tables.

Also I understand that when you concatenate a table to another, the second one won't be created in memory as an independent table so if you try to store those ones QlikView won't be able to find them.

So, if you need to store Table_B and Table_C you must load those again without using Concatenate before the 'LOAD' string.

Function Concatenate and Store

pgalvezt — Fri, 06 May 2011 17:24:15 GMT

Thanks both of you.

I'm sorry I forgot put the Load command in the second example table. But I understood the problem that I had. Now,

How can I fix the synthetics keys problem in another way? Of course my dashboard works fine, but in the table visor its shows many tables and many associations.

Any Suggestions.

Best regards

Function Concatenate and Store

Fri, 06 May 2011 17:27:26 GMT

How can you have synthetic keys if the above script only creates one table?

Do you have more tables?

Function Concatenate and Store

pgalvezt — Fri, 06 May 2011 17:32:02 GMT

I have 13 Tables. The association is created with the Date field.

Function Concatenate and Store

Fri, 06 May 2011 17:35:52 GMT

I forgot my suggestion:

You can always rename the fields that are not keys, or try to create unique keys concatenating multiple fields into one:

Filed_1 & Field_2 & Field_3 as Key_Fields

Function Concatenate and Store

pgalvezt — Fri, 06 May 2011 18:22:15 GMT

Finally I did this:

Concatenated all and Store I put:

Store Table_A Into QVD\Table_B.qvd;

Store Table_A Into QVD\Table_C.qvd;

Store Table_A Into QVD\Table_D.qvd;

and so on;

Table visor shows Now: Only One Table. And I have now 13 files qvds

Thanks both of you.

Function Concatenate and Store

erichshiino — Fri, 06 May 2011 18:24:25 GMT

Did you check your QVDs? Aren't they all the same?

Function Concatenate and Store

pgalvezt — Fri, 06 May 2011 18:50:09 GMT

I was on it, you´re right all the qvd's files are the same. mmm I think I'm ging to eliminate the Store Function.

If you have another alternative please let me know thanks.

Function Concatenate and Store

Fri, 06 May 2011 19:07:26 GMT

Since all your fields are called the same I think you could You could also concatenate the latest tables with a flag so that after you store table A, you can do resident loads from it using where and looking for the flags you set before. That way you'll store only the records that belong to each different table.

Table_A:

Load *, '1' AS FLAG

From

;

STORE Table_A into QVD\Table_A.qvd;

concatenate (Table_A)

LOAD *, , '2' AS FLAG

From

;

concatenate (Table_A)

LOAD * , '3' AS FLAG

From

;

Table_B:

LOAD *

RESIDENT Table_A

WHERE FLAG='2';

STORE Table_B into QVD\Table_B.qvd;

Drop TABLE Table_B;

Table_C:

LOAD *

RESIDENT Table_A

WHERE FLAG='3';

STORE Table_C into QVD\Table_C.qvd;

Drop TABLE Table_C;

Re: Function Concatenate and Store

erichshiino — Fri, 06 May 2011 19:15:13 GMT

This will probably work.

Just include a noconcatenate before creating Table_B from table A. Since

they have the same fields, QlikView will auto-concatenate them.

Adjust your code to something like this:

noconcatenate

Table_B:

LOAD *

RESIDENT Table_A

WHERE FLAG='2';

STORE Table_B into QVD\Table_B.qvd;

Drop TABLE Table_B;

(reply by email!)