How to fill up blanks in table box?

[#1]

Hi,

I have sample data as below in excel.

Zipcode      PartZipcode       CustomerName
WE9 9YU      WE9               Adam
WE9 8IO       WE9               Adam
WE9 4HJ      WE9
WE9 0TY      WE9
WE9 6YH      WE9
SG6 6CV      SG6               Railey
SG6 2EF      SG6               Railey
SG6 8IU       SG6               Railey
SG6 0PL      SG6
SG6 4SD      SG6
TH78 8BN    TH78              Maron
TH78 8IO     TH78              Maron
TH78 2DF    TH78             
TH78 4RF    TH78
TH78 3BN    TH78
TH78 5ZX    TH78

As I have millions of data rows. Would it be possible to fill the blanks in CustomerName using Qlikview file. Please can anyone suggest me.

Thanks.

[prma7799]

Hi,

Yes its possible to fill the blank rows while fetching the file you have to do some activity below.

Thanks,

Prashant

[Anonymous]

How about something like this in you load script ?

     if ( len(CustomerName) > 0 , CustomerName , 'Your Blank Value')     as CustomerName ,

[#4]

Thanks. Can you please explain a bit more clear how to use this.

[#5]

What text you want to fill the blank with?

If the blank to be filled with a constant value, you can do that while loading the data. Find below an example assuming the input is a text file.

Data1:

LOAD Zipcode,

     PartZipcode,

     If(isnull(CustomerName) or Trim(CustomerName)='', 'None', CustomerName) as CustomerName

FROM (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

[#6]

Thanks. what I need to replace under 'Your Blank Value'.

Please suggest

[#7]

Thanks. I need to fill the CustomerName in blanks which are above blanks when there is PartZipcode matches with Zipcode.

EDIT: for eg: when the Zipcode=WE9 the CustomerName should show Adam without any blanks.

Please suggest me.

[#8 · Accepted solution]

You need to create a mapping of partzipcode with customername in this case. Use the below script

Map1:

Mapping LOAD Distinct PartZipcode, CustomerName

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Data2:

LOAD Zipcode,

     PartZipcode,

     If(isnull(CustomerName) or Trim(CustomerName)='', ApplyMap('Map1',PartZipcode), CustomerName) as CustomerName

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

[#9]

Thanks. How can I change the script if in there are 5 more columns in excel file as below

Officename, Date, GoodsID, Region

sorry I haven't add these in before

Please suggest.

[#10]

Thanks I got it to add the remaining columns.