Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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;

2 Replies
MVP
MVP

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

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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

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