table is not dropped

[#1]

Hi,

I have noticed that sometimes the tables get dropped when I have not given a drop table command and sometimes they are not dropped wven when I have give the drop table command.

Please take a look at the code below:

po_rc_temp:
LOAD "po_number" AS PO,
"po_oldregrate",
"po_newregrate",
"po_oldotrate",
"po_newotrate",
date(floor(po_effectivedate),'MM/DD/YYYY') as po_effectivedate,
"po_user";
SQL SELECT *
FROM "TMS_Data".dbo."po_rc";

po_rc:
LOAD *,
date(po_effectivedate - previous(po_effectivedate), 'MM/DD/YYYY') as po_eff_prev
resident po_rc_temp order by PO, po_effectivedate;

Drop Table Timesheets;
Drop table po_rc_temp;

somehow the table po_rc_temp is not getting dropped because of which i am getting synthetic keys. i am sure i am missing something. can anyone help me please?

thanks,

[#2]

Hi

I THINK (think being the operative word, not tested this) that the reason is you are doing a LOAD * in the po_rc table, this means that you will be loading the same field names and therefore the load will be concatenated to po_rc_temp.

Try renaming your fields upon load as follows:

po_rc:
LOAD PO AS po_number
,po_oldregrate as oldregrate
etc, etc.

Your drop command should then work because po_rc will be a table in its own right.

[#3]

Hi Nigel,

Thanks for the reply! Tried that but did not work. Finally, started from an earlier version of the file and that worked correctly. Looks like the file/data got corrupted somehow. Yesterday, it (same file) was dropping a table on its own.

regards,

[sparur · Accepted solution]

Hello you can order in SQL SELECT statement and create field po_eff_prev in first LOAD statement:

po_rc_temp:
LOAD "po_number" AS PO,
"po_oldregrate",
"po_newregrate",
"po_oldotrate",
"po_newotrate",
date(floor(po_effectivedate),'MM/DD/YYYY') as po_effectivedate,
"po_user",

date(po_effectivedate - previous(po_effectivedate), 'MM/DD/YYYY') as po_eff_prev

;
SQL SELECT *
FROM "TMS_Data".dbo."po_rc" ORDER BY "po_number", po_effectivedate;

so you don't need to drop tmpTable and you win in a time.

[#5]

Hi Sparur,

Thanks for the reply!

However 'order by' only works for a previously loaded table.

please take a look at the para from page 312 of the manual:

"order by is a clause used to sort the records of a resident input table
before they are processed by the load statement. The resident table
can be sorted by one or more fields in ascending or descending
order. The sorting is made primarily by numeric value and secondarily
by national ASCII value. This clause may only be used when
the data source is a resident table."

regards.

[kji]

Order by as a qlikview script command needs to be used in resident load, however order by is also a SQL command and can be used as in the example above.

[#7]

Hi Johan,

I did try that (and I tried again) before using a resident load in my sql statement but it gave me sql error. that was precisely the reason I used a resident load. may be i missed something somewhere.

thanks,

manoj

[#8]

Hi,

You are right. i must be missing something when i tried that earlier. anyway, now i am using joins to achieve something else so i need to have a resident table anyway.

as you can see i am relatively new.

thanks,