Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Below is the code of our data:
Load * inline [
First, Second
A,EE
-,BB
B,-
C,FF
F,GG
G,CC
E,-
-, AA]
The output of the above code is:
Now I want to match second column values with the first column values and the output should be as:
First | Second |
A | AA |
- | - |
B | BB |
C | CC |
F | FF |
G | GG |
E | EE |
- | - |
The changes should be implemented in the script editor, and the above data is created for just 7 columns extract and the data is huge in length in reality.
@aishan07 try below
Data:
Load * inline [
First, Second
A,EE
-,BB
B,-
C,FF
F,GG
G,CC
E,-
-, AA];
All_values:
LOAD FieldValue('First',RecNo()) as First_temp
AutoGenerate FieldValueCount('First');
Join(All_values)
LOAD FieldValue('Second',RecNo()) as Second_temp
AutoGenerate FieldValueCount('Second');
Left Join(Data)
LOAD First_temp as First,
Second_temp as Second_new
Resident All_values
where Index(Second_temp,First_temp);
DROP Table All_values;
please explain in more detail. it looks as if you just put value of First in Second.
please elaborate on your logic
load First
,First as Second
;
Load * inline [
First, Second
A,E
-,B
B,-
C,F
F,G
G,C
E,-
-, A];
I want to match the second column with the first column. When the value in the second column is the same as the first column, it should put it next to it. So it matches column first's A with A of the second column. It is much more about string matching.
@aishan07 try below
Data:
Load * inline [
First, Second
A,EE
-,BB
B,-
C,FF
F,GG
G,CC
E,-
-, AA];
All_values:
LOAD FieldValue('First',RecNo()) as First_temp
AutoGenerate FieldValueCount('First');
Join(All_values)
LOAD FieldValue('Second',RecNo()) as Second_temp
AutoGenerate FieldValueCount('Second');
Left Join(Data)
LOAD First_temp as First,
Second_temp as Second_new
Resident All_values
where Index(Second_temp,First_temp);
DROP Table All_values;
Great..This seems like a perfect solution.
Could you please explain this code:
LOAD FieldValue('First',RecNo()) as First_temp
AutoGenerate FieldValueCount('First')