Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
reanfadyl
Partner - Creator
Partner - Creator

Load *; on top of SQL SELECT * not working

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

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

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

View solution in original post

10 Replies
Chanty4u
MVP
MVP

wat error ur getting?

kkkumar82
Specialist III
Specialist III

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

sunny_talwar

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?

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

awhitfield
Partner - Champion
Partner - Champion

Have you tried the 3rd query in SQL?

Andy

Clever_Anjos
Employee
Employee

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

awhitfield
Partner - Champion
Partner - Champion

You'll get ambiguous results!

reanfadyl
Partner - Creator
Partner - Creator
Author

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

reanfadyl
Partner - Creator
Partner - Creator
Author

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));