Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Replacing missed fields

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

Untitled.png

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

8 Replies
sunny_talwar

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;

Not applicable
Author

hi sunny,



it is not working

EXAMPLE.png


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






sunny_talwar

Would you be able to share a sample in Excel?

Not applicable
Author

Hi sunny,

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

thanks,

mani

sunny_talwar

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);

Not applicable
Author

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

sunny_talwar

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;

View solution in original post

Not applicable
Author

Thank you sunny it is perfect.