Generating data for missing rows

[#1]

Hi,

I have a data set in which data is missing in some rows in Rating column. Some data is unique whereas some data is repeating. I want to fill the repeating data with the same values in each row and leave the unique values as it is because there is no mapping for the unique values.

Ex : There is a blank cell for Star Cruise, I want to fill it with 4 whereas for Aquafina I wish to leave it blank.

Please help, its very urgent.

Thanks

[ahaahaaha]

Hi,

May be like this (look attached file)

Table1:

LOAD*Inline

[Cruise, Rating

Star Cruise, 4

Star Cruise,

Star Cruise, 4

Star Cruise, 4

Aquafina Lego,

Malaysian, 3

Malaysian,

Malaysian,

Malaysian,

Malaysian,

Malaysian,

Malaysian,

Malaysian, 3];

Table2:

NoConcatenate

LOAD

Cruise,

Max(Rating) as MaxRating

Resident Table1

Group By Cruise;

Left Join (Table1)

LOAD

Cruise,

MaxRating

Resident Table2;

DROP Table Table2;

Result

Regards,

Andrey

[vinieme12]

Just this would suffice i believe

LOAD Cruise,

     if(len(trim(Rating))<1,if(Cruise = Previous(Cruise),peek(Rating),Rating),Rating) as Rating,

     rowno() as ID

From XXXXxxxx;

[vinieme12 · Accepted solution]

the above is provided your data is sorted by Cruise and Rating

else you have to use order by on a Resident load as below

table1:

LOAD Cruise,

     Rating,

     rowno() as ID

FROM <<YourSource>>

NOCONCATENATE

LOAD Cruise,

     if(len(trim(Rating))<1,if(Cruise = Previous(Cruise),peek(Rating),Rating),Rating) as Rating,

    ID

Resident  table1

Order by Cruise,Rating DESC;

drop table table1;