topic Re: Droping tables in the script in QlikView

Droping tables in the script

Thu, 05 Jun 2014 19:32:08 GMT

Hi,

I created 4 qvd's and pulled into another qvw file, but not sure where to drop the tables in the script.

ALL:

LOAD * from IMF.qvd(qvd);

JOIN LOAD * from WIF.qvd(qvd);

JOIN LOAD * from DIL.qvd(qvd);

JOIN LOAD * from DIH.qvd(qvd);

//DROP TABLE IMF;

//DROP TABLE WIF;

//DROP TABLE DIL;

//DROP TABLE DIH;

FINAL:

LOAD * RESIDENT ALL;

//DROP TABLE ALL;

I see ALL after executing the script, but I suppose to see FINAL table in the table viewer. And where to drop the tables?

Thanks,

Re: Droping tables in the script

rustyfishbones — Thu, 05 Jun 2014 19:37:38 GMT

Should you not be storing the ALL tables into a new QVD and then dropping the tables?

ALL:

LOAD * from IMF.qvd(qvd);

JOIN LOAD * from WIF.qvd(qvd);

JOIN LOAD * from DIL.qvd(qvd);

JOIN LOAD * from DIH.qvd(qvd);

STORE ALL INTO ALL.qvd(qvd);

//DROP TABLE IMF;

//DROP TABLE WIF;

//DROP TABLE DIL;

//DROP TABLE DIH;

FINAL:

LOAD * RESIDENT ALL;

//DROP TABLE ALL;

Re: Droping tables in the script

Clever_Anjos — Thu, 05 Jun 2014 19:39:49 GMT

If you´re joining you don´t need to drop original tables

FINAL:

LOAD * RESIDENT ALL;

FINAL will not be generated because share same fields from ALL, so QlikView will concatenate records to ALL table

Re: Droping tables in the script

eduardo_sommer — Thu, 05 Jun 2014 19:42:38 GMT

The problem is that your Load * from ALL, concatenates the result to the ALL table, ignoring the FINAL name. You can use a Noconcatenate Load * Resident All to prevent this. This will correct your script.

This is Qlikview's behavior (to concatenate tables with the same structur, unless you explicitly uses noconcatenate)

Eduardo

Re: Droping tables in the script

eduardo_sommer — Thu, 05 Jun 2014 19:43:35 GMT

Clever is right. I couldn't see the need to load the data again.

Eduardo

Re: Droping tables in the script

MK_QSL — Thu, 05 Jun 2014 19:44:52 GMT

ALL:

LOAD * from IMF.qvd(qvd);

JOIN LOAD * from WIF.qvd(qvd);

JOIN LOAD * from DIL.qvd(qvd);

JOIN LOAD * from DIH.qvd(qvd);

STORE ALL into ALL.qvd (QVD);

Drop Table ALL;

FINAL:

Load * FROM ALL;

=========================

Hope this helps..

Re: Droping tables in the script

Peter_Cammaert — Thu, 05 Jun 2014 21:18:03 GMT

No, this isn't "QlikView behavior" because QlikView should respect the different table name (per the documentation). It's an unfortunate effect that QlikTech refuses to fix for a very long time. For one reason or another...

But of course you're right. Add NOCONCATENATE to the FINAL table LOAD statement and the problem fixes itself.

I don't know why but we seem to keep adding keywords and statements to otherwise straightforward grammar-compliant scripts that succumb to side-effects...

Re: Droping tables in the script

Fri, 06 Jun 2014 05:40:47 GMT

Thanks All,

The reason behind using FINAL is I am creating few calculated fields.

ALL:

LOAD * from IMF.qvd(qvd);

JOIN LOAD * from WIF.qvd(qvd);

JOIN LOAD * from DIL.qvd(qvd);

JOIN LOAD * from DIH.qvd(qvd);

STORE ALL INTO ALL.qvd(qvd);

DROP TABLE ALL;

FINAL:

LOAD *,

few calculated fields here

RESIDENT ALL;

Got error message table not found, Even I tried with Load *, calculated fields from ALL;

Didn't work???

Thanks,

Re: Droping tables in the script

jonathandienst — Fri, 06 Jun 2014 05:53:33 GMT

Well after you drop table ALL, then it's no longer resident. You would need a load from the qvd rather than from a resident table.. Or else use NoConcatenate as per several replies, and drop ALL after the resident load.

HTH

Jonathan

Re: Droping tables in the script

its_anandrjs — Fri, 06 Jun 2014 05:55:15 GMT

When you create one QVD based on four table files and create single table as ALL then after this call ALL.qvd not ALL which is in the application and as you use Drop table ALL it will not found. So open the ALL.qvd and make calculated fields see the load statement

ALL:

LOAD * from IMF.qvd(qvd);

JOIN LOAD * from WIF.qvd(qvd);

JOIN LOAD * from DIL.qvd(qvd);

JOIN LOAD * from DIH.qvd(qvd);

STORE ALL INTO ALL.qvd(qvd);

DROP TABLE ALL;

Noconcatenate //This is optional try top check while i believe not used here but try

FINAL:

LOAD *,

few calculated fields here

From ALL.qvd(qvd);

Re: Droping tables in the script

Fri, 06 Jun 2014 06:32:19 GMT

Yep Anand, the syntax working fine without no concatenate.

Thanks,

Re: Droping tables in the script

ankit777 — Fri, 06 Jun 2014 07:47:18 GMT

Hi ,

You can use this.

ALL:

LOAD * from IMF.qvd(qvd);

JOIN LOAD * from WIF.qvd(qvd);

JOIN LOAD * from DIL.qvd(qvd);

JOIN LOAD * from DIH.qvd(qvd);

DROP TABLES IMF,WIF,DIL,DIH;

NOCONCATENATE
FINAL:

LOAD * RESIDENT ALL;

DROP TABLE ALL;

Re: Droping tables in the script

Sun, 08 Jun 2014 18:08:56 GMT

ALL:

LOAD * from IMF.qvd(qvd);

JOIN LOAD * from WIF.qvd(qvd);

JOIN LOAD * from DIL.qvd(qvd);

JOIN LOAD * from DIH.qvd(qvd);

The above script making join among the tables, because I am getting double the records, not sure whether it is doing join or not?

Thanks,

Re: Droping tables in the script

Sun, 08 Jun 2014 18:22:11 GMT

Use NoConcatenate in front of LOAD which is being used for FINAL table as shown below.

ALL:

LOAD * from IMF.qvd(qvd);

JOIN LOAD * from WIF.qvd(qvd);

JOIN LOAD * from DIL.qvd(qvd);

JOIN LOAD * from DIH.qvd(qvd);

//DROP TABLE IMF;

//DROP TABLE WIF;

//DROP TABLE DIL;

//DROP TABLE DIH;

FINAL:

NoConcatenate LOAD * RESIDENT ALL;

DROP TABLE ALL;

REM : Reason behind having only ALL table is, when you are using Load * on FINAL table, the FINAL table is getting concatenated with ALL table, becuase qlikview automatically concatenates the 2nd load table, if it sees that 2nd table has all the fields with same name;

Re: Droping tables in the script

Sun, 08 Jun 2014 18:32:10 GMT

I am dropping the ALL table before final load right, so still do we need No concatenate?

Thanks,