Performance preceding load on a KEEP

[NickHoff]

I've exhausted most of my resources on this issue.  The statement below runs correctly, but it takes an extremely long time to run, and we do have some powerful production servers.  If I attempt to do a preceding load, and SUM factors i'm still forced to keep Factors in the original load, which un-summed will take just as long to load.  If I keep SUM(Factors) i'm forced to do a GROUP BY.  What i'm looking for is a solution to improve my load time on a table that contains 500+ million records.

LEFT KEEP(PatientFact)

LOAD PatientDWID,

     APPLYMAP('MapRoomType',RevenueCode,NULL()) AS RoomType,

     RevenueCode,

     RevenueCodeDesc,

     ChargeHCPCSCode,

     SUM(Factors)

FROM

[..\..\..\1.Common\1.2.QVD\1.2.1.Stage1\1.2.1.Common_RevCodeSummary_CDC.qvd](qvd)

WHERE EXISTS (RevenueCode,RevenueCode)

GROUP BY PatientDWID, RevenueCode, RevenueCodeDesc,ChargeHCPCSCode;

STORE RoomFactors INTO [..\..\5.2.QVD\5.2.2.Stage2\5.2.2.ICU_RevCodeFactorSummary.qvd]

(qvd);

Here is the preceding load i've attempted.

RoomFactors:

LOAD

  SUM(Factors) AS FactorsSUM

  GROUP BY PatientDWID, RevenueCode;

LEFT KEEP(PatientFact)

LOAD PatientDWID,

     APPLYMAP('MapRoomType',RevenueCode,NULL()) AS RoomType,

     RevenueCode,

     RevenueCodeDesc,

     ChargeHCPCSCode,

     Factors

FROM

[..\..\..\1.Common\1.2.QVD\1.2.1.Stage1\1.2.1.Common_RevCodeSummary_CDC.qvd](qvd)

WHERE EXISTS (RevenueCode,RevenueCode);

STORE RoomFactors INTO [..\..\5.2.QVD\5.2.2.Stage2\5.2.2.ICU_RevCodeFactorSummary.qvd]

(qvd);

[Gysbert_Wassenaar]

One thing you can try is replacing the WHERE EXISTS (RevenueCode,RevenueCode) with a left keep too. See this discussion: http://community.qlik.com/message/588837#588837

[jerrysvensson]

Since you are doing where exists on fields with the same name, you should remove the first RevenueCode.

Like : WHERE EXISTS (RevenueCode);

This makes a difference.

[NickHoff]

That wouldn't work for what i'm doing because the first RevenueCode is looking everywhere else in the application for RevenueCode, the second matches the first with what's in the current table.

[NickHoff]

I'm already doing a LEFT KEEP on the table with the WHERE EXISTS.  I need the WHERE EXISTS because it hits a filter table to limit the results.

[jerrysvensson]

Have you tried doing it in two steps instead of preceeding load?

A couple of days ago I had a similar situation with a preceeding load and aggregation.

It was very slow, I instead used a temporary table and resident load.

I had about 1000 rows so maybe it will not be the same for you.

[NickHoff · Accepted solution]

I ended up creating and using a summary table for the rev code with factors summed up.  That way performing the aggregation ahead of time I didn't have to use a GROUP by.