topic Re: Load tables in QlikView

Load tables

satishqlik — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I've been trying to load all tables from database

and creating QVDs automatically but not working properly.

If I add [] its getting an error and It working be fine if the table name has no spaces.

Suggest me on this where am wrong.

Tablelist:

LOAD*

Inline

[Tablename

Re: Load tables

sunny_talwar — Sat, 30 Jul 2016 13:12:08 GMT

How about this:

Tablelist:

LOAD * Inline[

Tablename

Categories

Customers

Divisions

Order Details

Products

Shippers

Shipments

];

FOR i=1 to FieldValueCount('Tablename')

LET Vtable = '[' & FieldValue('Tablename', $(i)) & ']';

SQL SELECT *

FROM $(Vtable);

SET VQvdPath= D:\Practice\Data\QVDS\;

STORE $(Vtable) into $(VQvdPath)$(Vtable).qvd(qvd);

NEXT

Re: Load tables

satishqlik — Sat, 30 Jul 2016 13:59:25 GMT

Thanks Sunny but not working still its getting an error. PFB for your ref.

Re: Load tables

sunny_talwar — Sat, 30 Jul 2016 14:14:56 GMT

Did you also fix the i -> $(i)?

Re: Load tables

sunny_talwar — Sat, 30 Jul 2016 14:15:36 GMT

Also try this:

FOR i=1 to FieldValueCount('Tablename')

LET Vtable = FieldValue('Tablename', $(i));

SQL SELECT *

FROM $(Vtable);

SET VQvdPath= D:\Practice\Data\QVDS\;

STORE $(Vtable) into $(VQvdPath)$(Vtable).qvd(qvd);

NEXT

Re: Load tables

satishqlik — Sat, 30 Jul 2016 14:43:29 GMT

Yes! Its working fine if the table name has no spaces.

But what about Order Details table?

Re: Load tables

sunny_talwar — Sat, 30 Jul 2016 14:51:17 GMT

Would you be able to share the application?

Re: Load tables

satishqlik — Sat, 30 Jul 2016 15:05:04 GMT

PFB of your reference. !

Re: Load tables

sunny_talwar — Sat, 30 Jul 2016 15:08:51 GMT

So this for sure wasn't working:

FOR i=1 to FieldValueCount('Tablename')

LET Vtable = '[' & FieldValue('Tablename', $(i)) & ']';

SQL SELECT *

FROM $(Vtable);

SET VQvdPath= D:\Practice\Data\QVDS\;

STORE $(Vtable) into $(VQvdPath)$(Vtable).qvd(qvd);

NEXT

Above we have the square brackets and also $(i) instead of i?

Re: Load tables

antoniotiman — Sat, 30 Jul 2016 16:43:09 GMT

Hi Satish,

try

SQL SELECT * FROM "$(Vtable)";

and

Store "$(Vtable)" ..........

Remove []

Regards,

Antonio

Re: Load tables

maxgro — Sat, 30 Jul 2016 17:15:35 GMT

this works in my test

SET VQvdPath= D:\Practice\Data\QVDS\;

FOR i=1 to FieldValueCount('Tablename')

LET Vtable = FieldValue('Tablename', $(i));

//trace $(Vtable);

[$(Vtable)]:

NoConcatenate LOAD *;

SQL SELECT * FROM [$(Vtable)];

STORE [$(Vtable)] into [$(VQvdPath)$(Vtable).qvd] (qvd);

NEXT

Re: Load tables

satishqlik — Sat, 30 Jul 2016 18:32:41 GMT

Sorry for my late response!

Finally I got all the tables in the data model along with Order Details( Syn table) table But why QVDs were not generated?Any Idea?

Re: Load tables

sunny_talwar — Sat, 30 Jul 2016 18:39:02 GMT

Same table name? Not sure I understand your question. Would you be able to elaborate?

Re: Load tables

satishqlik — Sat, 30 Jul 2016 18:47:53 GMT

Sunny there were no QVDs in the mentioned QvdPath it is an empty..Why it is?

Re: Load tables

sunny_talwar — Sat, 30 Jul 2016 18:51:17 GMT

Can you try this:

FOR i=1 to FieldValueCount('Tablename')

LET Vtable = '[' & FieldValue('Tablename', $(i)) & ']';

$(Vtable):

SQL SELECT *

FROM $(Vtable);

SET VQvdPath= D:\Practice\Data\QVDS\;

STORE $(Vtable) into $(VQvdPath)$(Vtable).qvd(qvd);

NEXT

FOR i=1 to FieldValueCount('Tablename')

LET Vtable = '[' & FieldValue('Tablename', $(i)) & ']';

LET VtableStore = FieldValue('Tablename', $(i));

$(Vtable):

SQL SELECT *

FROM $(Vtable);

SET VQvdPath= D:\Practice\Data\QVDS\;

STORE $(Vtable) into $(VQvdPath)$(VtableStore).qvd(qvd);

NEXT

Re: Load tables

satishqlik — Sat, 30 Jul 2016 18:52:31 GMT

Thanks maxgro its working great.

But am unable to understand your expression in that why did you use NOConcatenate keyword.?

If you don't mind,Would you be able to elaborate little bit more?

Thannks Again!

Re: Load tables

satishqlik — Sat, 30 Jul 2016 18:59:54 GMT

Thanks Sunny for your effort now your expression is working great!!!

Re: Load tables

maxgro — Sat, 30 Jul 2016 22:00:32 GMT

May be you have 2 source tables with the same names and number of fields. Qlik will concatenate the tables and I don't want this. So I add a noconcatenate. Another way is to add a drop table after the store.