Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aj0031724
Partner - Creator
Partner - Creator

Exclude X % of call model from master table in fatstest way

DEAR TEAM,

Requirement is -> Dump those model devices from Devices master table that is not contributing X % of call.

X% IS VARIABLE USER DEFINED:

LET SAY X=0.01

Existing code AS BELOW that we have TOOK almost 2 hours to dump few thouannds model from Million models.

Can u please advise what is the fastest/optimized way of achieving the  same results?

tHIS IS HOW WE ARE HANDLING AT PRESENT WHICH IS VERY TIME CONSUMING

/12/2016 3:37:54 AM: 0087      SumAllCallsTmp:

7/12/2016 3:37:54 AM: 0088         LOAD

7/12/2016 3:37:54 AM: 0089         sum(COUNT) as SumOfAllCalls

7/12/2016 3:37:54 AM: 0090         Resident DEVICEMASTER

7/12/2016 3:42:09 AM:            1 fields found: SumOfAllCalls, 1 lines fetched

7/12/2016 3:42:09 AM: 0093      LET vSumAllCalls=Peek('SumOfAllCalls',0)

7/12/2016 3:42:09 AM: 0094      DROP Table SumAllCallsTmp

7/12/2016 3:42:09 AM: 0097      ModelDumpCalc:

7/12/2016 3:42:09 AM: 0098         LOAD

7/12/2016 3:42:09 AM: 0099          ModelDevices,

7/12/2016 3:42:09 AM: 0100             sum(COUNT)/vSumAllCalls as SumCallPerModel

7/12/2016 3:42:09 AM: 0101         Resident DEVICEMASTER

7/12/2016 3:42:09 AM: 0102         group by ModelDevices

7/12/2016 3:47:37 AM:            2 fields found: ModelDevices, SumCallPerModel, 29,314 lines fetched

7/12/2016 3:47:37 AM: 0103      DROP Table DEVICEMASTER

7/12/2016 3:47:46 AM: 0106      NoConcatenate

7/12/2016 3:47:46 AM: 0107        ModelDumpOrderBy:

7/12/2016 3:47:46 AM: 0108         LOAD *

7/12/2016 3:47:46 AM: 0109         Resident ModelDumpCalc

7/12/2016 3:47:46 AM: 0110         order by SumCallPerModel asc

7/12/2016 3:47:46 AM:            2 fields found: ModelDevices, SumCallPerModel, 29,314 lines fetched

7/12/2016 3:47:46 AM: 0111      DROP Table ModelDumpCalc

7/12/2016 3:47:46 AM: 0114      NoConcatenate

7/12/2016 3:47:46 AM: 0115        AccumulatedTable:

7/12/2016 3:47:46 AM: 0116         LOAD *, if(rowno()=1,SumCallPerModel,Peek(AccumulatedSum)+SumCallPerModel) AS AccumulatedSum

7/12/2016 3:47:46 AM: 0117         Resident ModelDumpOrderBy

7/12/2016 3:47:46 AM:            3 fields found: ModelDevices, SumCallPerModel, AccumulatedSum, 29,314 lines fetched

7/12/2016 3:47:46 AM: 0118      DROP Table ModelDumpOrderBy

7/12/2016 3:47:46 AM: 0121      NoConcatenate

7/12/2016 3:47:46 AM: 0122        ModelDump:

7/12/2016 3:47:46 AM: 0123         LOAD *

7/12/2016 3:47:46 AM: 0124         Resident AccumulatedTable

7/12/2016 3:47:46 AM: 0125         where  AccumulatedSum>0.01

7/12/2016 3:47:46 AM:            3 fields found: ModelDevices, SumCallPerModel, AccumulatedSum, 2,541 lines fetched

7/12/2016 3:47:46 AM: 0126      DROP Table AccumulatedTable

7/12/2016 3:47:46 AM: 0130      left keep(ModelDump)

7/12/2016 3:47:46 AM: 0131        DEVICEMASTER:

7/12/2016 3:47:46 AM: 0132         LOAD Distinct

7/12/2016 3:47:46 AM: 0133           *

7/12/2016 3:47:46 AM: 0134         FROM F:\QV\ScModel\QVD\Devices\DEVICEMASTER.Qvd(qvd)

7/12/2016 3:47:46 AM:            24 fields found:461,875,007 lines fetched

7/12/2016 4:58:52 AM:           Joining/Keeping

7/12/2016 5:04:47 AM: 0136      DROP Table ModelDump

1 Reply
sunny_talwar

May be use Where Exists() instead of Left Keep because you will be able to do an optimized load while loading the qvd using Where Exists() whereas Left Keep() might not keep it optimized (I am not sure if Left keep is optimized or not)