how to show two column values in one row

[kavita25]

Hi,

sunindia‌

I want to show column1 values and 2nd column in one row..

For e.g

Id  Column1   Column2

1      125             -

1       300             -

1        -                  520

1        -                  452

Result:

Id  Column1   Column2

1      125              452

2       300             520

Please help me with this issue.

Regards,

Kavita

[kavita25]

@jontydkpi

Ids are same, as you can see in the sample data..ids are same..but there are null values for opposite column..So i want both the column value in one row...without nulls.

[kavita25]

sunindia‌

please help me with this issue..I tried but m unable to succeed.

[#4]

Hi Kavita,

Can you please try using below script. And let me know is this you are expecting and take the result table as output.

temp:

load * inline [

Id , Column1 ,  Column2

1,125,

2,300,

1,,520

2,,452

];

NoConcatenate

result:

load Id as id1, max(Column1) as Col1, max(Column2) as Col2

Resident temp group by Id;

Thanks,

Sreeman

[sunny_talwar]

May be this:

Table1:

LOAD *,

  RowNo() as Key;

LOAD * Inline [

Id, Column1

1, 125

1, 300

];

Table2:

LOAD *,

  RowNo() as Key;

LOAD * Inline [

Id, Column2

1, 520

1, 452

];

[sathishkumar_go]

Hi Kavita,

Try this....

Test:
LOAD * INLINE [
Id,  Column1,   Column2
1, 125, -
2, 300, -
1, , 520
2, , 452
];

NoConcatenate
Test1:
load
Id,
Column1
resident Test where len(Column1) >1;

left join (Test1)
load
Id,
Column2
resident Test where len(Column2) >1;;

drop table Test;

-Sathish

[sunny_talwar]

If the below suggestion doesn't work out, would you be able to share your source Excel files so that we can try playing around with script and do some refresh.

[kavita25]

PFA

[PradeepReddy]

'Vas' excel sheet, has only 2 columns, is there any other columns apart from these 2 columns?

[kavita25]

See the attached qvw.

[kavita25]

Please see the attached qvw and files.