topic Loading a selection from a database based upon whats already loaded. in QlikView

Loading a selection from a database based upon whats already loaded.

Fri, 09 Nov 2012 14:27:03 GMT

Hello I have a database I wish to use to create some data associations which are only held within the DB. I am having problems using the 'where exists Clause' as it tells me that the tables dont exist after the database loads. Here is the code I am using as an example.

Also please see attached.

Customer_Table :
LOAD
BP

FROM

(qvd);

OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0; etc etc

Mosiac_raw:
LOAD *,
LEFT([Client Reference],10) AS BP;
SQL SELECT `Client Reference`,
    `pc_mosaic_public_sector_group_alpha`,
    `pc_mosaic_public_sector_group_num`,
    `pc_mosaic_public_sector_type`
FROM `Water_Append_Output`
where exists(BP,LEFT([Client Reference],10))
;

Mosaic:
LOAD BP,
min(pc_mosaic_public_sector_type) as Type
Resident Mosiac_raw
Group By BP;

inner Join(Mosaic)

LOAD BP,
pc_mosaic_public_sector_type as Type,
pc_mosaic_public_sector_group_alpha & num(pc_mosaic_public_sector_type,'00') as Code
Resident Mosiac_raw;

drop Table Mosiac_raw;

Re: Loading a selection from a database based upon whats already loaded.

jonathandienst — Fri, 09 Nov 2012 14:34:39 GMT

Your Where Exists is in the SQL section of the script, which is processed by the Jet driver. This is not a valid SQL command that Jet recognises, hence the failure. Try this instead:

Mosiac_raw:

LOAD *,

LEFT([Client Reference],10) AS BP

where exists(BP,LEFT([Client Reference],10))

;

SQL SELECT `Client Reference`,

`pc_mosaic_public_sector_group_alpha`,

`pc_mosaic_public_sector_group_num`,

`pc_mosaic_public_sector_type`

FROM `Water_Append_Output`

;

Hope that helps

Jonathan

Re: Loading a selection from a database based upon whats already loaded.

Fri, 09 Nov 2012 17:26:58 GMT

Hello,

can you try to run the script without the creation of the table "Mosaic" and check if the table "Mosaic_raw" is correct?

You could have an error on "Mosaic_raw" that leads your error on "Mosaic"

Regards,

Daniele