Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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