Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
cohenj3_
New Contributor III

inner keep

Hi,

I am trying to reduce loads by using INNER KEEP (<table>).

(Inner joins  which reduce number of rows loaded for subsequent tables).

Tried all kind of syntax combinations, but its not working.

Please note that I am loading from SQL, not qvd.

Thanks

1 Solution

Accepted Solutions
cohenj3_
New Contributor III

Re: inner keep

This works, but its slow.  (the INNER KEEP is not needed)

Table1:

LOAD Account AS PatientAccountID,

    
PatientClass,

    
ADDate,

    
Discharged,

    
LOS,

    
Provider,

    
PrimaryDX,

    
PrimaryProcedure,

    
ECD,

    
NerveBlock,

    
FPxDate

     

FROM

$(vPathName)xxxxyyyyy.xls

(
biff, embedded labels, table is KWMatch$);





Table2:

INNER KEEP (Table1)

LOAD   [PatientAccountID]

      ,
[ObjectID] AS PatientVisitOID

      ,
[VisitDSC]

      ,
[StartDTS]

      ,
[StartDTS] AS VisitDate

       ,
[EndDTS]

       ,
[VisitTypeCD]

      ,
[CategoryDSC]

      ,
[FinancialClassCD]

      ,
[UnitContactedNM]

      ,
[DischargeDispositionDSC]

      ,
[AccommodationTypeCD]

      ,
[DischargeToDSC]

      ,
[AdmitReasonCD]

      ,
[PreVisitDTS]

      ,
[AdmitSourceDSC]

      ,
[EDWLastModifiedDTS]

WHERE Exists(PatientAccountID);



     
SQL SELECT *

FROM SQL.DBO.zzzzzz
where [IsDeletedFLG] = 0 and DatePart(Year, [StartDTS]) = 2014

order by ObjectID DESC;

14 Replies

Re: inner keep

Select A.* from TableA as A

inner join TableB as B on A.fld1 = B.fld1

let me know

cohenj3_
New Contributor III

Re: inner keep

Will check it out.

I thought the “ON” is only a SQL syntax, not a QlikView scripting.

Thanks

Re: inner keep

Yes it is, I thought you need help on SQL (as you said)

You can use in the script the code I sent to you.

cohenj3_
New Contributor III

Re: inner keep

Hi Alessandro,

I am loading a list of items from Excel, then I want to load from a SQL table (20 million rows), only the rows that include these items.

So I thought of having a Qlikview “INNER KEEP” in the script, to reduce the rows loaded.

J

Re: inner keep

So you have to

1) load the Excel list

2) LEFT Join the table from sql

in this way only records matchink the Excel list will be taken (no way to reduce data before loading the sql)

cohenj3_
New Contributor III

Re: inner keep

Yes, that’s what I am realizing.

However, it can be done with EXISTS

Not applicable

Re: inner keep

Try this one.


Table1:

Select * from Table1;

Table2:

Inner keep(Table1)

Select * from Table2;

If its not working, please post the error which you are getting.

Re: inner keep

Yes but the Whole set of data must be read from sql ... where exists ...

Gabriel
Valued Contributor II

Re: inner keep

Hi,

I think you should look into using EXISTS function in your WHERE clause

Community Browser