topic Load ; on top of SQL SELECT not working in QlikView

Load ; on top of SQL SELECT not working

reanfadyl — Sun, 17 Apr 2016 23:05:09 GMT

Hi Experts,

Can someone please answer why the third example of load scripting below doesn't work?

The first two work fine, but the third one creates a bunch of $Orphan tables, as opposed to one PATIENTS_V3 table.

/ ----------------------------------------

// Load the PATIENTS data

// ----------------------------------------

//Works version 1

PATIENTS_V1:

LOAD *;

SQL SELECT *

FROM PIMS.PATIENTS

Where

MODIF_DTTM > to_date('$(vModifyDateTime)','DD/MM/YYYY');

//Works version 2

PATIENTS_V2:

LOAD

"PATNT_REFNO",

"SEXXX_REFNO",

FORENAME;

SQL SELECT *

from

PIMS.PATIENTS patnt,

PIMS.PROVIDER_SPELLS prvsp

where PATNT.PATNT_REFNO=PRVSP.PATNT_REFNO

and nvl(PRVSP.ARCHV_FLAG,'N')='N'

and PRVSP.MODIF_DTTM > sysdate-101;

//Doesn't Work???

PATIENTS_V3:

LOAD *;

SQL SELECT *

from

PIMS.PATIENTS patnt,

PIMS.PROVIDER_SPELLS prvsp

where PATNT.PATNT_REFNO=PRVSP.PATNT_REFNO

and nvl(PRVSP.ARCHV_FLAG,'N')='N'

and PRVSP.MODIF_DTTM > sysdate-101;

Cheers

Rean

Re: Load ; on top of SQL SELECT not working

Chanty4u — Mon, 18 Apr 2016 08:52:39 GMT

wat error ur getting?

Re: Load ; on top of SQL SELECT not working

kkkumar82 — Mon, 18 Apr 2016 08:53:28 GMT

What is the error your are getting?? can you share the image

Re: Load ; on top of SQL SELECT not working

sunny_talwar — Mon, 18 Apr 2016 08:57:35 GMT

Are you loading all the three tables in one script or are you just loading one of the three tables? Can you share a screenshot of the tables that get created?

Re: Load ; on top of SQL SELECT not working

Peter_Cammaert — Mon, 18 Apr 2016 08:59:49 GMT

Check if you're using a CROSSTABLE prefix somewhere...

Re: Load ; on top of SQL SELECT not working

awhitfield — Mon, 18 Apr 2016 11:26:14 GMT

Have you tried the 3rd query in SQL?

Andy

Re: Load ; on top of SQL SELECT not working

Clever_Anjos — Mon, 18 Apr 2016 11:33:29 GMT

I´m guessing that one of your SELECT * retrieve more than one column with same name.

QlikView does not allow a LOAD with two (or more) columns with duplicated names

Re: Load ; on top of SQL SELECT not working

awhitfield — Mon, 18 Apr 2016 11:43:27 GMT

You'll get ambiguous results!

Re: Load ; on top of SQL SELECT not working

reanfadyl — Mon, 18 Apr 2016 19:17:10 GMT

Thanks, this makes sense, I will test and post up if it fixes the issue.

Re: Load ; on top of SQL SELECT not working

reanfadyl — Mon, 18 Apr 2016 23:26:19 GMT

Hi All,

The below worked, as suggested by Anjos I think my issue was being caused by duplicate columns returned by the SELECT * acting across multiple tables with common field names.

The below worked:

Patients:

Load *;

select

patnt.*

,prvsp.prvsp_refno

,refrl.refrl_refno

from PIMS.patients patnt,

PIMS.provider_spells prvsp,

PIMS.referrals refrl

where prvsp.patnt_refno=patnt.patnt_refno

and prvsp.refrl_refno=refrl.refrl_refno

and nvl(prvsp.archv_flag,'N') = 'N'

and (prvsp.modif_dttm > sysdate -101 or refrl.modif_dttm > sysdate-101)

union

select

patnt.*

, null

, refrl.refrl_refno

from PIMS.patients patnt,

PIMS.referrals refrl

where refrl.patnt_refno=patnt.patnt_refno

and nvl(refrl.archv_flag,'N') = 'N'

and refrl.modif_dttm > sysdate -101

and not exists (

select null from

PIMS.provider_spells prvsp2

where prvsp2.refrl_refno=refrl.refrl_refno

and nvl(prvsp2.archv_flag,'N') = 'N'

and (prvsp2.modif_dttm > sysdate -101 or refrl.modif_dttm > sysdate -101));

Re: Load ; on top of SQL SELECT not working

vikramv — Tue, 19 Apr 2016 00:37:12 GMT

Use Like this

SELECT patnt.* , prvsp.*

from

PIMS.PATIENTS patnt,

PIMS.PROVIDER_SPELLS prvsp

where PATNT.PATNT_REFNO=PRVSP.PATNT_REFNO

and nvl(PRVSP.ARCHV_FLAG,'N')='N'

and PRVSP.MODIF_DTTM > sysdate-101;

topic Load *; on top of SQL SELECT * not working in QlikView

Load *; on top of SQL SELECT * not working

Re: Load *; on top of SQL SELECT * not working

Re: Load *; on top of SQL SELECT * not working

Re: Load *; on top of SQL SELECT * not working

Re: Load *; on top of SQL SELECT * not working

Re: Load *; on top of SQL SELECT * not working

Re: Load *; on top of SQL SELECT * not working

Re: Load *; on top of SQL SELECT * not working

Re: Load *; on top of SQL SELECT * not working

Re: Load *; on top of SQL SELECT * not working

Re: Load *; on top of SQL SELECT * not working

topic Load ; on top of SQL SELECT not working in QlikView

Load ; on top of SQL SELECT not working

Re: Load ; on top of SQL SELECT not working

Re: Load ; on top of SQL SELECT not working

Re: Load ; on top of SQL SELECT not working

Re: Load ; on top of SQL SELECT not working

Re: Load ; on top of SQL SELECT not working

Re: Load ; on top of SQL SELECT not working

Re: Load ; on top of SQL SELECT not working

Re: Load ; on top of SQL SELECT not working

Re: Load ; on top of SQL SELECT not working

Re: Load ; on top of SQL SELECT not working