topic Re: Fill empty cells with value from "above" in QlikView

Fill empty cells with value from "above"

Tue, 22 Oct 2013 12:09:52 GMT

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.

Re: Fill empty cells with value from "above"

israrkhan — Tue, 22 Oct 2013 12:13:49 GMT

post your script...or try like below

load

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

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

Re: Fill empty cells with value from "above"

MayilVahanan — Tue, 22 Oct 2013 12:17:23 GMT

Try like this

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

Re: Re: Fill empty cells with value from "above"

Tue, 22 Oct 2013 12:18:03 GMT

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

.... ( ... );

Re: Fill empty cells with value from "above"

Tue, 22 Oct 2013 12:18:25 GMT

Hi,

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

In Cell Conditions : Choose 'is empty'

Re: Re: Fill empty cells with value from "above"

Tue, 22 Oct 2013 12:22:12 GMT

Thank you!!

Re: Fill empty cells with value from "above"

israrkhan — Tue, 22 Oct 2013 12:23:11 GMT

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]

Re: Fill empty cells with value from "above"

rbecher — Tue, 22 Oct 2013 12:24:14 GMT

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

Re: Fill empty cells with value from "above"

Anonymous — Tue, 23 May 2017 02:11:28 GMT

Similar answer here Excel file with merged cells to be imported