topic Needs help in loop within chart in QlikView

Needs help in loop within chart

mohammedsleem — Mon, 14 Dec 2015 05:11:06 GMT

Hi All,

I created the below example to show my case. Lets say that I have these two inline tables:

[Subjects]:
LOAD * INLINE [
    Subject, ValueOf
    'Subject 123', 12
    'Subject 456', 30
    'Subject 789', 46
    'Subject 101', 22
    'Subject 111', 51
];

The above inline table contains the subjects and each subject value.

[HandelRates]:
LOAD * INLINE [
    FromValue, ToValue, Rate
    1, 25, 1
    26, 40, 2
    41, 60, 3
    61, 80, 4
    81, 100, 5
];

the above inline table contains the rates of subjects. so in case the subject X has value 15 so its rate is 1 because 15 is between 1 and 25.

So I need to have a chart (table) as shown below:

with each "Subject" I should show the "Rate" based on the subject's "ValueOf". If the "ValueOf" is between the "FromValue" and "ToValue" then show the "Rate".

I added the sample in the attachments.

any one can help me with this? please advice.

Re: Needs help in loop within chart

vikasmahajan — Mon, 14 Dec 2015 09:32:17 GMT

try with Interval Match function

Vikas

Re: Needs help in loop within chart

MK_QSL — Mon, 14 Dec 2015 09:36:10 GMT

Subjects:

LOAD * INLINE

[

Subject, ValueOf

'Subject 123', 12

'Subject 456', 30

'Subject 789', 46

'Subject 101', 22

'Subject 111', 51

];

HandelRates:

LOAD * INLINE

[

FromValue, ToValue, Rate

1, 25, 1

26, 40, 2

41, 60, 3

61, 80, 4

81, 100, 5

];

Left Join

IntervalMatch(ValueOf) Load

FromValue, ToValue

Resident HandelRates;

Left Join (Subjects)

Load * Resident HandelRates;

Drop Table HandelRates;

Re: Needs help in loop within chart

tamilarasu — Mon, 14 Dec 2015 09:38:10 GMT

Hi Mohammed,

Try,

HandelRates:
LOAD * INLINE [
    FromValue, ToValue, Rate
    1, 25, 1
    26, 40, 2
    41, 60, 3
    61, 80, 4
    81, 100, 5
];


Subjects:
LOAD * INLINE [
    Subject, ValueOf
    'Subject 123', 12
    'Subject 456', 30
    'Subject 789', 46
    'Subject 101', 22
    'Subject 111', 51
];


LEFT JOIN (Subjects)
IntervalMatch(ValueOf)
LOAD FromValue,   
     ToValue  
RESIDENT HandelRates;  
LEFT JOIN (Subjects) 
LOAD *   
RESIDENT HandelRates;  
DROP Table HandelRates;

Re: Needs help in loop within chart

veidlburkhard — Mon, 14 Dec 2015 10:26:44 GMT

Hi Mohammed,

as an alternative to IntervalMatch function you can do it like this:

Hope this helps.

Burkhard

Re: Needs help in loop within chart

mohammedsleem — Mon, 14 Dec 2015 10:41:06 GMT

Thank you all for the replies.

I need to achieve this through the QlikView set analysis.

This should be done by the user. I don't need to do it in the script itself.

is their any way to do that?

Re: Needs help in loop within chart

mohammedsleem — Mon, 14 Dec 2015 11:30:39 GMT

I can say that I need the result to be looks like below but using set analysis or expression

Re: Needs help in loop within chart

MK_QSL — Mon, 14 Dec 2015 12:01:32 GMT

Yes, you can follow

veidlburkhard‌suggestion.

Re: Needs help in loop within chart

mohammedsleem — Mon, 14 Dec 2015 12:06:46 GMT

Burkhard Veidl's suggestion is valid in case the data of FromValueand ToValue are static but in my case these values are dynamic and retrievable from a data source.

Re: Needs help in loop within chart

mohammedsleem — Mon, 14 Dec 2015 14:46:36 GMT

dears,

any idea. this is my first post here

I really needs help for this issue.

Re: Needs help in loop within chart

mohammedsleem — Wed, 16 Dec 2015 04:27:45 GMT

Dear,

Can ValueList or ValueLoop fix my issue?

Awaiting for your kind help and support.

Re: Needs help in loop within chart

jagan — Wed, 16 Dec 2015 05:16:09 GMT

Hi,

Try like this

HandelRates:

LOAD * INLINE [

FromValue, ToValue, Rate

1, 25, 1

26, 40, 2

41, 60, 3

61, 80, 4

81, 100, 5

];

Subjects:

LOAD * INLINE [

Subject, ValueOf

'Subject 123', 12

'Subject 456', 30

'Subject 789', 46

'Subject 101', 22

'Subject 111', 51

];

Chart : Straight Table

Dimension: Subject, ValueOf

Expression: =Aggr(If([ValueOf] >= FromValue AND [ValueOf] <= ToValue, Rate), FromValue, ToValue, Subject)

But if possible handle this in scirpt by using IntervalMatch() otherwise you will get performance issues if data is huge.

Hope this helps you.

Regards,

jagan.