Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

conditional statement, using multi-row formula

Afrooz
7 - Meteor

Hi everyone 

I have this sample data and I'm trying to write condition using multi-row.formula tool

 

The condition is if [Field] = 1 then put the first row  in [New Field] to 0. It might be groups of 1 but I want only the first row of each group to be 0 else null().

 

Requirements: the data is for a time period and each customer ID can have different number of records.

so it has to be grouped by customer ID.

also if ones are end of the observation (if customer ID changes after the last '1' , highlighted row) then null() I don't need that to be 0. 

 

I hope I'm not too confusing. 

I really appreciate your help

 

Thanks 

Afrooz

8 REPLIES 8
ashissanpui
9 - Comet

@Afrooz Here are the steps

1. Use record id at source

2. sort by Customer ID(ascending), Field (ascending), date(ascending)

3. Use multi row formula and group by customer id, field, date

4. Sue the formula in MRF if [cust ID] = [row-1:Cust ID] then null() else 0 endif

5. sort record id y ascending.

 

hope this will resolve your issue

 

Ashis

afv2688
16 - Nebula
16 - Nebula

Hello @Afrooz ,

 

This does it:

 

🙂Untitled.png

 

Edit: corrected, thanks @Jonathan-Sherman 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @Afrooz,

 

I think this depnds how your data is structured, if all records where [Field] = 1 for each [Customer ID] are grouped together then @afv2688's solution would work perfectly, however if not i'm not sure it would work if not all 1's are grouped together as in the example picture you attached where there are two groups of 1's for [Customer ID] = 1?

 

For example:

Customer IDField
11
12
11
11

 

This might become:

Customer IDFieldNew Field
111
120
111
110

 

 

To solve this issue you could use:

 

image.png

 

Otherwise @afv2688 would work perfectly!

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Regards,

Jonathan

Afrooz
7 - Meteor

Hi @Jonathon-Sherman and @afv2688

 

thanks for your solutions.  

in the data set there might be more than one group of 1s, both solutions give me the same result which are not quiet right, 

  • There can be more than one group of 1s for one customer ID
  • or it might be only one row with [Field] = 1 or even none

Both solutions return 0 for every group of 1 which is correct, but my problem is that i don't want 0 if that group of 1 leads to the end of the observation, and the customer ID changes after that last group of 1s. 

 

For example in @Jonathan-Sherman solution the only correct answer is row 7.

In customer ID 1 and 2 New Field should not be zero because all the values in [Field] = 1 

 

I'm sorry if I sound too confusing

would be great if you could help me with. 

thank you very much for your time

 

Regards, 

Afrooz

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @Afrooz,

 

I've started from scratch and gone a slightly different way which i believe solves what you're trying to achieve? I've also taken into account where there may be more than 2 "groups" of 1s, i'm only assigning the value of 0 to the first record in the group of the last group where the group doesn't lead to the end of the observation (i've added Customer ID 5 to illustrate my point for this case) if that makes sense? 

 

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Regards,

Jonathan

Afrooz
7 - Meteor

@Jonathan-Sherman 

 

Thank you  very much for the effort you put into this. I'm nearly there just need to get 0 in New Field in similar cases to row 21, in your example. 

I you could tell me which part of to change i should be able to do it. 

 

 

Thank you so so much

 

Afrooz

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @Afrooz,

In that case I've amended the workflow slightly to give you this result:

 

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Regards,

Jonathan

Afrooz
7 - Meteor

Thank you very much @Jonathan-Sherman 

 

I got the answer and it works perfect. 

One last thing, could you please explain a little what's happening in each step. i just want to make sure I have a good understanding of it.

 

 

Regards'

Afrooz 

Labels