- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Can we use "for loop" in Column Formula ?
Hi All,
Is there any way to combine these two operations (Creating a new columns and then running a ForLoop) to a single one ?
I have repeated Customer Ids in a columns and then it changes at some point and 2nd ID keeps on repeating and then 3rd one and so on. I want to create a new column say, Unq_Id and its values should be empty until Customer_Id changes. I can do this in script below, but I am wondering if there is any better way to do it ? This means - running a loop inside Column Formula, or something similar !
dt << New Column( “Unq_ID”, Numeric, Continuous, Set Each Value() );
dt << Sort( by( :Customer_ID ), Order( Ascending ), Replace Table( 1 ) );
For( i = 1, i <= N Rows( dt ), i++,
If(
:Customer_ID[i] == :Customer_ID[i + 1]
,
:Unq_ID[i] = :Customer_ID[i]
)
);
Any suggestion?
thanks, Hari
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Can we use "for loop" in Column Formula ?
Here are a couple of ways to create the column you want
Names Default To Here( 1 );
dt = New Table( "custIDs",
New Column( "Customer_ID", character, values( {"c1", "c1", "c1", "c2", "c2", "c3", "c3", "c3"} ) )
);
dt << New Column( "Or like this" );
counter = 0;
For( i = 1, i <= N Rows( dt ), i++,
If(
i == N Rows( dt ),
counter++;
:or like this[i] = counter;,
:Customer_ID[i] != :Customer_ID[i + 1],
counter++;
:or like this[i] = counter;
)
);
// or
dt << New Column( "or like this using formula",
formula(
x = .;
If( Row() == 1, counter = 0 );
If( :Customer_ID != Lag( :Customer_ID, -1 ) | Row() == N Rows( Current Data Table() ),
counter++;
x = counter;
);
x;
)
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Can we use "for loop" in Column Formula ?
Here is one way of doing it
Names Default To Here( 1 );
dt = New Table( "custIDs",
New Column( "Customer_ID",
character,
values( {"c1", "c1", "c1", "c2", "c2", "c3", "c3", "c3"} )
)
);
For( i = 1, i <= N Rows( dt ), i++,
If( i == 1,
dt << New Column( Char( :Customer_ID[i] ), character );
Column( dt, :Customer_ID[i] )[i] = :Customer_ID[i];
,
If( :Customer_ID[i] != :Customer_ID[i - 1],
dt << New Column( Char( :Customer_ID[i] ), character )
)
);
Column( dt, :Customer_ID[i] )[i] = :Customer_ID[i];
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Can we use "for loop" in Column Formula ?
Hi Nelson, I though about that but it is not what I am looking for.
The script you provided is giving me something like below. And the one I am looking for is inside the red tringle.
Also, rather than putting c1, c2 .... how to put 1, 2, 3 ... sort of counter, which increases by 1 as soon as there is a change.
Any help?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Can we use "for loop" in Column Formula ?
Here are a couple of ways to create the column you want
Names Default To Here( 1 );
dt = New Table( "custIDs",
New Column( "Customer_ID", character, values( {"c1", "c1", "c1", "c2", "c2", "c3", "c3", "c3"} ) )
);
dt << New Column( "Or like this" );
counter = 0;
For( i = 1, i <= N Rows( dt ), i++,
If(
i == N Rows( dt ),
counter++;
:or like this[i] = counter;,
:Customer_ID[i] != :Customer_ID[i + 1],
counter++;
:or like this[i] = counter;
)
);
// or
dt << New Column( "or like this using formula",
formula(
x = .;
If( Row() == 1, counter = 0 );
If( :Customer_ID != Lag( :Customer_ID, -1 ) | Row() == N Rows( Current Data Table() ),
counter++;
x = counter;
);
x;
)
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Can we use "for loop" in Column Formula ?
Yes, I was looking for the 2nd type of solution.
Thanks.