topic Re: Generating data for missing rows in QlikView

Generating data for missing rows

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Generating data for missing rows

ahaahaaha — Mon, 05 Jun 2017 11:21:00 GMT

Hi,

May be like this (look attached file)

Table1:

LOAD*Inline

[Cruise, Rating

Star Cruise, 4

Star Cruise,

Star Cruise, 4

Aquafina Lego,

Malaysian, 3

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

Re: Generating data for missing rows

vinieme12 — Mon, 05 Jun 2017 11:25:58 GMT

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;

Re: Generating data for missing rows

vinieme12 — Mon, 05 Jun 2017 11:31:48 GMT

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,

Resident table1

Order by Cruise,Rating DESC;

drop table table1;