Range based on a table

[#1]

Hi,

I have a table (Instruments) having columns: InstID, LGD, Currency, Amount and another table LGDRange that stores the range information for LGD.

LGDRange:

Min Max Range

0 10 Range10

11 21 Range21

.....

I am suppose to generate a graph for chart defined in the table. Now what I did is: (1) Load LGD Range table (2) Load Instrument table as InstTemp (3) Loop through Range table for number of rows in it and find out which columns fit which range. Script is as below:

InstTemp:

LOAD
InstID,
LGD,
UCurrency,
NetCommitment
FROM

(qvd);

For vCounter = 0 To (FieldValueCount('LGDRange')-1)

Let vLGDMin = peek('LGDMin', vCounter, 'RangeLGD');
Let vLGDMax = peek('LGDMax', vCounter, 'RangeLGD');
Let vRangeLGD = peek('LGDRange',vCounter, 'RangeLGD');
Inst:
NOCONCATENATE
Load
*,
peek('LGDRange',vCounter, 'RangeLGD') as RangeLGD
RESIDENT InstTemp
WHERE
LossGivenDefaultPercent >= vLGDMin And
LossGivenDefaultPercent < vLGDMax;
Next vCounter;

drop table InstTemp;

I tried debug and everything works fine till it exits the loop. During the debug it shows that each loop fetched how many rows and the no. shown is as expected.

As soon as exits the loop, the QV stops responding. I have to kill QV. Just wondering what is happening here and why is it not responding. If the comment out the loop, everything works fine.

[#2]

Just small correction, the variables are used as $(vLGDMin) and so on.

[Miguel_Angel_Baeyens]

Hello,

At a first glance, you are using a NOCONCATENATE for each table loaded inside the loop. Depending on your amount of records, it may cause QlikView to keep calculating a very large number of synth keys, since the fields for each table inside the loop are exactly the same, and since noconcatenate does not prevent to create those synthetic keys which, in your case, I'm guessing are not wanted.

As this process is done in memory, QlikView doesn't show any external sign apart from, I'd say, an unusual memory and cpu usage, which can cause your application to break.

Hope that helps

[#4]

Thanks Minguel.

Not sure how, but the issue of QV getting stuck was solved. I just logged off and logged in agan to QV.

And yes, NOCONCATENATE was causing 20 tables getting created and a lot synthetic keys. This is working fine now.

There is one more thing which is causing perf issue however. I am just thinking how can I get around it. I have around 10M rows and 20 ranges. Now all these 10M rows have to be passed through the loop 20 times. This causing perf problems.

What I think I can do in the loop:

Load
InstID,
LGD,
UCurrency,
NetCommitment
FROM

(qvd);

For vCounter = 0 To (FieldValueCount('LGDRange')-1)

Let vLGDMin = peek('LGDMin', vCounter, 'RangeLGD');
Let vLGDMax = peek('LGDMax', vCounter, 'RangeLGD');
Let vRangeLGD = peek('LGDRange',vCounter, 'RangeLGD');

Inst:
Load
InstID as InstIDForRange,
'$(vRangeLGD) as LGDRange
RESIDENT InstTemp
WHERE
LossGivenDefaultPercent >= $(vLGDMin) And
LossGivenDefaultPercent < $(vLGDMax) And
InstID not exists (InstIDForRange, InstID);
Next vCounter;

Not sure if it would work as the field InstIDForRange would work during the first loop. I am going to give it a try.

Any suggestion if there is a better way to avoid 10M * 10 loops for performance?

[#5]

I think IntervalMatch prefix could help you a lot here.

Steve

[#6]

Thanks Steve. It has worked wonders and performance is much better now. However the problem is - model created has a lot of synthetic keys.

I check the help of this function IntervalMatch and in the example, it does not mentions anything about the systhetic keys etc.

I would see if I can do anything about it now.

[#7]

By the way I have found one more work around. while building QVD files, I can write the inner join and can add Range cols there itself.

I am going to try and see if I can overcome this synth key issue for IntervalMatch.

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

Check some posts regarding intervalmatch where complete examples are developed (this one or this other one for instance). Since a new table is created as a result of that intervalmatch, some synth keys will appear and you will need to drop some tables or fields once your tables are linked properly.

Regards.

[#9]

Thanks Miguel. It is working now. I had to add some joins and some drop field statements. Copying the final code here for ref.

LGDRange:
NOCONCATENATE
Load
RangeName as LGDRangeName,
MinRange as LGDMin,
MaxRange as LGDMax,
Description as LGDRangeDesc,
MinRange & '_' & MaxRange as LGDJoinKey
RESIDENT
Range
WHERE AttribName = 'LGDRange';

Instrument_Res:
FIRST 10000
LOAD RFInstrumentID,
//convert from decimal to %
LGD * 100 as LGD,
FROM
F:\Final QVDs\InstrumentResults_201003.qvd (qvd);

//**** Step through Instrument table for each LGD range and locate the LGd range
InstLGD1:
LEFT JOIN (Instrument_Res) IntervalMatch ( LGD ) LOAD (LGDMin + 0.00001) as LGDMin, (LGDMax + 0.0001) as LGDMax RESIDENT LGDRange;

InstLGD2:
INNER JOIN (Instrument_Res) LOAD RFInstrumentID, (LGDMin & '_' & LGDMax) as LGDJoinKey RESIDENT Instrument_Res;
Drop fields LGDMin, LGDMax FROM Instrument_Res;

InstLGD3:
LEFT JOIN (Instrument_Res) LOAD LGDJoinKey, LGDRangeName RESIDENT LGDRange;
Drop fields LGDJoinKey;