topic Re: Replacing missed fields in Connectivity & Data Prep

Replacing missed fields

Tue, 16 Feb 2016 06:35:01 GMT

Hi I'm new to qliksense can any one help me out from this issue.

When i'm loading data the some cells are empty ,i want to fill those cells with above row cell data.is it possible in qliksense ..?

Thanks

Re: Replacing missed fields

sunny_talwar — Tue, 16 Feb 2016 09:54:52 GMT

May be using Peek() function:

Table:

LOAD Field1,

Field2

FROM Source;

FinalTable:

LOAD Field1,

If(Peek('Field1') = Field1 and Len(Trim(Field2)) = 0, Peek('Field2'), Field2) as Field2

Resident Table

Order By Field1, Field2 desc;

DROP Table Table;

Re: Replacing missed fields

Wed, 17 Feb 2016 05:59:38 GMT

hi sunny,

it is not working

This is a example table ,in the above table i want to place the empty cell into name with the above cell name and pic the min time and max time in a day for each person.

(For referring the names id's are available)

Thank,

Mani

Re: Replacing missed fields

sunny_talwar — Wed, 17 Feb 2016 13:25:27 GMT

Would you be able to share a sample in Excel?

Re: Replacing missed fields

Wed, 17 Feb 2016 13:56:36 GMT

Hi sunny,

below i shared the example file..just have a look into that.

thanks,

mani

Re: Replacing missed fields

sunny_talwar — Thu, 18 Feb 2016 11:22:05 GMT

Try this script:

Table:

LOAD ID,

If(Len(Trim(NAME)) = 0, Peek('NAME'), NAME) as NAME,

DATE,

TIME

FROM

[Book1 (7).xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Replacing missed fields

Thu, 18 Feb 2016 13:48:14 GMT

Thank you sunny,it's working correctly.can you send the code for picking start time and end time in a day for each person for the same table data

i tried with below code but its not working

data1:

load

ID,

min(TIME) as mintime,

max(TIME) as maxtime

resident tablename

group by NAME;

thanks,

Mani

Re: Replacing missed fields

sunny_talwar — Thu, 18 Feb 2016 18:40:41 GMT

Try this:

Table:

LOAD ID,

If(Len(Trim(NAME)) = 0, Peek('NAME'), NAME) as NAME,

DATE,

TIME

FROM

[Book1 (7).xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join (Table)

LOAD NAME,

DATE,

Time(Max(TIME), 'hh:mm') as MAXTIME,

Time(Min(TIME), 'hh:mm') as MINTIME

Resident Table

Group By NAME, DATE;

Re: Replacing missed fields

Fri, 19 Feb 2016 05:41:26 GMT

Thank you sunny it is perfect.