Change dynamically data in column

[User93]

Hi all,

I need help on something I'm trying to implement to change some of the rows in a column.

Here is the case : I have three columns of data :
Column 1 : values A, B, C, D...
Column 2 : for each value of column 1, I have at least two values 1 and 2. Those values 1 and 2 are the same for each value of column 1.
Column 3 : I have one value for each value of column 2 : when column 2 = 1, column 3 = 1000, when column 2 = 2, column 3 = 2000, etc. Thse values however are different for each value of column 1.

Like this :

Column 1	Column 2	Column 3
A	1	1000
A	2	2000
B	1	1500
B	2	2500
C	1	1700
C	2	2700
D	1	5000
D	2	6000

I would like to do the following : for each value of column 1, I would like all rows of column 3 to show only the value for when column 2 = 1.
I don't want to filter on column 2 = 1 because I want to keep all the rows.

Like this :

Column 1	Column 2	Column 3
A	1	1000
A	2	1000
B	1	1500
B	2	1500
C	1	1700
C	2	1700
D	1	5000
D	2	5000

I tried to do it in the script :
IF(Column 2 = 1, Column 3) AS TEST

And I tried to do it directly in the table :
=Aggr({<Column 3 = {'1'}>}Column 2,Column 1)

But neither of this works.

Any idea on how to do this ?

Best regards.

[ogster1974]

In front end

Only{<[Column 2] = {'1'}>} [Column 3])

In Script 

Tmp:
Load * Inline [
Column 1, Column 2, Column 3,
A, 1, 1000
A, 2, 1000
B, 1, 1500
B, 2, 1500
C, 1, 1700
C, 2, 1700
D, 1, 5000
D, 2, 5000
]
;

Table:
Load
*,
If([Column 2]='1', [Column 3], Null()) as [Column 4]
Resident Tmp
;

Drop table Tmp;

End result of both.

[luciancotea]

Best way is to use the peek() function in the script.

[User93]

Hi, thank you for the quick answer.

However, I would like to show all the rows, including those where column 2 = 2, but with the new column showing the values for when column 2 = 1 on all rows (for each value of column 1),  like this, as in column 4 :

Column 1	Column 2	Column 3	Column 4
A	1	1000	1000
A	2	2000	1000
B	1	1500	1500
B	2	2500	1500
C	1	1700	1700
C	2	2700	1700
D	1	5000	5000
D	2	6000	5000

Is there a way to do it ?

[ogster1974 · Accepted solution]

Then this 

Load
*,
If([Column 1]=Previous([Column 1]),Previous([Column 3]),[Column 3]) as [Column 4]
Resident Tmp
;

[User93]

Thanks a lot, it's working !

Best regards.