topic Re: inner keep in QlikView

inner keep

Anonymous — Thu, 25 Jun 2015 12:38:41 GMT

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

Re: inner keep

alexandros17 — Thu, 25 Jun 2015 12:42:02 GMT

Select A.* from TableA as A

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

let me know

Re: inner keep

Anonymous — Thu, 25 Jun 2015 12:45:14 GMT

Will check it out.

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

Thanks

Re: inner keep

alexandros17 — Thu, 25 Jun 2015 12:49:58 GMT

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.

Re: inner keep

Anonymous — Thu, 25 Jun 2015 12:54:14 GMT

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.

Re: inner keep

alexandros17 — Thu, 25 Jun 2015 12:57:52 GMT

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)

Re: inner keep

Anonymous — Thu, 25 Jun 2015 12:59:38 GMT

Yes, that’s what I am realizing.

However, it can be done with EXISTS

Re: inner keep

Thu, 25 Jun 2015 13:01:10 GMT

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

alexandros17 — Thu, 25 Jun 2015 13:02:14 GMT

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

Re: inner keep

Gabriel — Thu, 25 Jun 2015 13:03:48 GMT

Hi,

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

Re: inner keep

Anonymous — Thu, 25 Jun 2015 13:05:02 GMT

Hi,

I did exactly that. Not getting an error, just the whole table loads, which I want to avoid.

Re: inner keep

Anonymous — Thu, 25 Jun 2015 13:08:38 GMT

That is working, but its slow.

Re: inner keep

Thu, 25 Jun 2015 13:10:08 GMT

Can you post your script or Qlikview file?

Re: inner keep

Anonymous — Thu, 25 Jun 2015 13:12:50 GMT

Ok.

I will do it a little later, because its loading now.. (30 minutes)

Re: inner keep

Anonymous — Thu, 25 Jun 2015 13:58:50 GMT

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;