topic URGENT: Group by condition and rangesum in script in QlikView

URGENT: Group by condition and rangesum in script

Wed, 16 Apr 2014 09:59:23 GMT

Hi All,

I have a problem that I am battling to figure out. Attached is the sample data with 2 additional fields preceded by the label 'desired'. These are the rules that need to be applied to correctly produce that value

Desired Output Rule

If the previous(PhoneNumber) is the same as the current PhoneNumber and not isnull(previous(FCRStatusValueFinal )) then sum all values in that group before the next null() value is encountered i.e. PhoneNumber 1234 has a value of 3 since all individual values meet the above condition and are summed.

Desired Key Rule

For each group, add a integer value to represent that.

Please, if anyone can assist with this it would be greatly appreciated. The reason for why I need it like this is because I want to then group by Key and Output and show how many 2, 3, 4, 5 callers there were. If you know of another way of getting this in script, by all means, please educate

Thanks,

Byron

Re: URGENT: Group by condition and rangesum in script

Wed, 16 Apr 2014 11:41:46 GMT

To add as an idea on how to achieve this, but still need someone to tell me how to do this

If we create a new date value and let it assume the first date for each grouped value

i,e,

DateOld, DateNew

01 Jan, 01 Jan

02 Jan, 01 Jan

04 Jan, 01 Jan

Then I would have PhoneNumber, DateNew to group by and sum values for to get the correct result.

Hope someone can attend to this shortly. Have to provide feedback in 2 hours

Cheers,

Byron

Re: Re: URGENT: Group by condition and rangesum in script

tresB — Wed, 16 Apr 2014 11:50:44 GMT

I just looked into your desired output(without reading in detail what you want(I tend to do so for long describtive posts)).

Try:

Input:
LOAD PhoneNumber,
    Date,
    FCRStatusValueFinalGroup,
    FCRStatusValueFinal
//    [Desired Output],
//    [Desired Key]
FROM
[........\Sample FCR Data.xls]
(biff, embedded labels, table is Sheet1$);

Left Join

LOAD
PhoneNumber,
AutoNumber( PhoneNumber) as DesiredKey,
Sum(FCRStatusValueFinal) as DesiredOutput
Resident Input group By PhoneNumber;

Re: URGENT: Group by condition and rangesum in script

Wed, 16 Apr 2014 12:54:26 GMT

Hi Tres,

Thanks for your reply. The solution you provided is close but does not include the one rule. Have a look at phone number 8901. That should be treated as 2 and 2, not 4.

Apologies for the long descriptive post, leaving out the detail would lead to an incorrect application of the rules.

I will take what you have done and see if I can figure out something with this. However, this post is still open Need as much help as possible

Cheers,

Byron