Fill empty cells with value from "above"

[#1]

Hello,

my problem is that i have a table like this:

Date	Value1	Value2
01.01.2013	20	30
	45	321
	213	23
	678	798
02.01.2013	123	123
	3232	3232
	578	953
	64421	7655

That is because of the formatting of the excel sheet is done by merging the date column. However qlikview only gets one value out of it.

Is there a way to fill these empty cells with the same Date as in the first row ??

I tried using "previous" in the load script but it only works for the next row after the first one.. the rest still stays empty..

thank your for your time.

[israrkhan]

post your script...or try like below

load

if(Date= ' ' or  isnull (Date), Previous(Date),Date) as Date,

or

if(Date= ' ' or  isnull (Date), Peek(Date),Date) as Date,

[MayilVahanan]

Hi

Try like this

Load Value1, Value2, If(Len(Trim(Date)) = 0, Peek('Date'), Date) AS Date From tablename;

[#4]

Here's the script...

CrossTable(Entscheid, Anzahl,8)

LOAD if(F2='',Previous(date(F2)),date(F2)) as Datum,

          num(F5) as Schicht,

          Time(F3) as von,

          Time(F4) as bis,

          Month (F2) as Monat,

          Year(F2) as Jahr,

          Bauteil,

          num([Run / Set]) as [Run / Set],

          iO,

          [Spritzer (Pulver / Overspray)],

          Spitting as Spitting,

          [Gewicht ausser Toleranz]

FROM

.... ( ... );

[#5 · Accepted solution]

Hi,

You can fill blank values with the import assistant of excel files.

In Cell Conditions : Choose 'is empty'

[#6]

Thank you!!

[israrkhan]

you are using date function one side in if statement...

so i think comparison goes wrong...

try this one...

CrossTable(Entscheid, Anzahl,8)

LOAD if(date(F2)=' ',Previous(date(F2)),date(F2)) as Datum,

          num(F5) as Schicht,

          Time(F3) as von,

          Time(F4) as bis,

          Month (F2) as Monat,

          Year(F2) as Jahr,

          Bauteil,

          num([Run / Set]) as [Run / Set],

          iO,

          [Spritzer (Pulver / Overspray)],

          Spitting as Spitting,

          [Gewicht ausser Toleranz]

[rbecher]

In script you can just use the peek() function:

LOAD

if(len(trim(Date))=0, peek(Date), Date) as Date,  // len(trim(<field>))=0 is the best way to catch NULL and empty string

Value1,

Value2

FROM...

- Ralf

[Anonymous]

Similar answer here      Excel file with merged cells to be imported