Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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.

reanfadyl
Not applicable

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
cuv
Not applicable

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

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

10 Replies
Chanty4u
Not applicable

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

wat error ur getting?

kkkumar82
Not applicable

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

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

sunny_talwar
Not applicable

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

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
Not applicable

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

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

awhitfield
Not applicable

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

Have you tried the 3rd query in SQL?

Andy

cuv
Not applicable

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

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
Not applicable

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

You'll get ambiguous results!

reanfadyl
Not applicable

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

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

reanfadyl
Not applicable

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

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