Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Then this
Load
*,
If([Column 1]=Previous([Column 1]),Previous([Column 3]),[Column 3]) as [Column 4]
Resident Tmp
;
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.
Best way is to use the peek() function in the script.
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 ?
Then this
Load
*,
If([Column 1]=Previous([Column 1]),Previous([Column 3]),[Column 3]) as [Column 4]
Resident Tmp
;
Thanks a lot, it's working !
Best regards.