Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
My Input,
KEY | TEXT1 | TEXT2 |
---|---|---|
1 | A | B |
2 | A | B |
3 | B | |
4 | A | |
5 |
Required Output,
KEY | OUTPUT |
---|---|
1 | A,B |
2 | A,B |
3 | B |
4 | A |
5 |
Need to do it in script,
As of now am using some nested if and & operator, do we have direct function??, can we do it with concat??
May be try this:
data:
CrossTable(TEXT, toto, 1)
LOAD * INLINE [
KEY, A, B,C
1, A, B,1
2, A, B
3, , B ,1
4, A, ,1
5
];
LOAD
KEY,
Concat(toto, ',', AutoNumber(TEXT)) as tt
Resident data
where len(toto)<>0
Group By KEY
;
You can transform your input table using CROSSTABLE LOAD prefix into a table with structure KEY, TEXT, and TEXT ID, then aggregate (using Concat() ) this new table TEXT values Grouped by KEY.
if(isnull(TEXT1),TEXT2,if(isnull(TEXT2),TEXT1,TEXT1&','&TEXT2))
this is what am doing as of now
i have many more columns and lakhs of rows like this, I dont think cross table can be formed for this
Hi,
If you have more than one field change the number 1
data:
CrossTable(TEXT, toto, 1)
LOAD * INLINE [
KEY, TEXT1, TEXT2
1, A, B
2, A, B
3, , B
4, A
5
];
LOAD
KEY,
Concat(toto, ', ') as tt
Resident data
Where Len(toto)<>0
Group By KEY
;
So what is the issue with this? This isn't working?
Does this working same as this from Preceding Load
(Text1 & ',' & Text2) as Output
Am looking for any direct function.. I will try out this way and let u know.. Thank u
This will give me an additional comma, when Text 2 is blank
Ya what i was doing is crct nly,, Just wanted to know is there any direct fuction for this??