Solved: Re: inner keep - Qlik Community

Anonymous · ‎2015-06-25

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

Anonymous · ‎2015-06-25

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;

View solution in original post

alexandros17 · ‎2015-06-25

Select A.* from TableA as A

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

let me know

Anonymous · ‎2015-06-25

Will check it out.

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

Thanks

alexandros17 · ‎2015-06-25

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.

Anonymous · ‎2015-06-25

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

alexandros17 · ‎2015-06-25

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)

Anonymous · ‎2015-06-25

Yes, that’s what I am realizing.

However, it can be done with EXISTS

Report Inappropriate Content · ‎2015-06-25

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.

alexandros17 · ‎2015-06-25

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

Gabriel · ‎2015-06-25

Hi,

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