topic Problems with the resident function in QlikView

Problems with the resident function

Mon, 27 Dec 2010 14:57:19 GMT

Dear QV users,

I wrote a script and I am using it now almost a year. Now I have some time to improve the script, so the upload can be faster. Below you find the old script;

Allocation:

Sql SELECT * from Jobscope.dbo.IpAlloc

WHERE

DATE_ISSUED >= '20090101' AND DATE_ISSUED <='20101231';

AllocTemp:

Load JOB_NUMBER, DATE_ISSUED, BATCH_NUMBER, ACCOUNT, STOCK_NUMBER, QUANTITY_ISSUED, EXTENDED_TOTAL

Resident Allocation;

Drop table Allocation;

I would like to create the following script:

Allocation:

Sql SELECT JOB_NUMBER, DATE_ISSUED, BATCH_NUMBER, ACCOUNT, STOCK_NUMBER, QUANTITY_ISSUED, EXTENDED_TOTAL

from Jobscope.dbo.IpAlloc WHERE DATE_ISSUED >= '20090101' AND DATE_ISSUED <='20101231';

AllocTemp:

Load * Resident Allocation;

Drop table Allocation;

When I use the new script the table AllocTemp does not exist anymore?

Can someone help me with this problem [:S]

With kind regards,

Aissam

Problems with the resident function

Mon, 27 Dec 2010 15:02:51 GMT

Hi.

Aren't you missing the LOAD statement at the beginning of the 'Allocation' table script?

Regards,

Montal.

Problems with the resident function

Mon, 27 Dec 2010 15:10:04 GMT

Hi Montal,

I don't need it, because it's SQL ( I think).

regards,

Aissam

Problems with the resident function

Mon, 27 Dec 2010 15:13:16 GMT

Hi.

Try to run the script only with the 'Allocation' part of the script, and see if you get no errors.

It still feels like the problem is in the 'Allocation' table...

Problems with the resident function

Mon, 27 Dec 2010 15:25:04 GMT

Hi Montal,

The allocation table works, I can see the fields defined. Attached you will find a print screen.

regards,

Aissam

SV:Problems with the resident function

gandalfgray — Mon, 27 Dec 2010 15:40:39 GMT

Hi Chiki

I think that the reason you don't get a AllocTemp table is that you create it as an exact copy of the Allocation table,

this will by default concatenate into Allocation instead of creating a new table.

Try:

AllocTemp:
Load *, 1 As Dummy Resident Allocation; // add a Dummy field to distingiush AllocTemp table from Allocation table
Drop Field Dummy;
Drop Table Allocation;

Problems with the resident function

syed_muzammil — Mon, 27 Dec 2010 15:44:39 GMT

Hi,

According to the Qlikview manual a load Statment is required before an SQL statement, i never tried it without a Load. But since you say its is working, the problem maybe that the Allocation Table and the Allocation Temp table having same Field names are being automatically concatenated by QlikView. Try using a No Concatenate Statement between the two Tables.

Problems with the resident function

Miguel_Angel_Baeyens — Mon, 27 Dec 2010 15:49:26 GMT

Hello,

Several things are happening here. In first place, you don't have to use the LOAD statement as QlikView does it implicitly when you load from a SQL statement, but the thing is that it's more clear and helps dbugging code and using all QlikView functions rather than raw SQL statements.

What it seems it's happening at the first sight is that you are loading the same table twice consecutively, first from SQL then from the already loaded table. That means both tables have the same exact number and name of fields, which results in QlikView implicitly concatenating both tables. When you concatenate one table to another, the result is always only one table.

So you load the table Allocation, then load from this to AllocTemp (being stored into Allocation) and then drop Allocation. Result: no records.

This can be avoided using NOCONCATENATE before the LOAD keyword. But if you only do this, as the fields remain named alike, you will have several unwanted synthetic keys. RESIDENT is likely working fine, and you can easily test that using

Qualify *;

before the LOAD statement, so your code looks like

Allocation:Sql SELECT JOB_NUMBER, DATE_ISSUED, BATCH_NUMBER, ACCOUNT, STOCK_NUMBER, QUANTITY_ISSUED, EXTENDED_TOTAL from Jobscope.dbo.IpAlloc WHERE DATE_ISSUED >= '20090101' AND DATE_ISSUED <='20101231'; Qualify *;AllocTemp:Load * Resident Allocation;Unqualify *; Drop table Allocation;

Qualifying will return all fields prefixed with the table name (for example "AllocTemp.JOB_NUMBER").

If this is correct, I don't see the need of loading from a resident table here, since you can rename fields in the first table (according to my first paragraph):

Allocation:LOAD JOB_NUMBER, DATE_ISSUED, BATCH_NUMBER, ACCOUNT, STOCK_NUMBER, QUANTITY_ISSUED, EXTENDED_TOTALSql SELECT JOB_NUMBER, DATE_ISSUED, BATCH_NUMBER, ACCOUNT, STOCK_NUMBER, QUANTITY_ISSUED, EXTENDED_TOTAL from Jobscope.dbo.IpAlloc WHERE DATE_ISSUED >= '20090101' AND DATE_ISSUED <='20101231';

But anyway.

Hope this helps

Problems with the resident function

Miguel_Angel_Baeyens — Mon, 27 Dec 2010 15:59:34 GMT

Just for clarification, LOAD is not mandatory before a SELECT, because it's done implicitly by QlikView, so

Table:SELECT field1 FROM Table;

will have the same effect that

Table:LOAD *;SELECT field1 FROM Table;

However I always use it and suggest to do so, as I said in my previous post. Since fields are case sensitive and the ODBC drivers may not parse some of the SQL commands, it's very powerful to use LOAD in any table you pull from data source.

The "Select..." dialog does all the work if you click on the "Preceding LOAD" check when using the wizard.

Problems with the resident function

Mon, 27 Dec 2010 16:26:04 GMT

Thanks guys,

I used the NOCONCATENATE Load statement and it works.

Thanks very much for your helps guys.

Regards,

Aissam