topic Re: Can we use "for loop" in Column Formula ? in Discussions

Can we use "for loop" in Column Formula ?

HSS — Fri, 09 Jun 2023 18:06:55 GMT

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

Re: Can we use "for loop" in Column Formula ?

txnelson — Wed, 08 Dec 2021 08:42:09 GMT

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];
);

Re: Can we use "for loop" in Column Formula ?

HSS — Wed, 08 Dec 2021 09:17:22 GMT

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?

Re: Can we use "for loop" in Column Formula ?

txnelson — Wed, 08 Dec 2021 12:07:20 GMT

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;
	)
);

Re: Can we use "for loop" in Column Formula ?

HSS — Wed, 08 Dec 2021 13:31:38 GMT

Yes, I was looking for the 2nd type of solution.
Thanks.