Fill column based when exporting data from excel

[anuradhaa]

    ID                    Date                    Boundary

39150479	Jan 3, 2013
39150969	Jan 3, 2013
33921553	Jan 3, 2013
41824919	Jan 3, 2013
40506013	Jan 7, 2013
41868133	Jan 8, 2013	Jan 8, 2013
42016041	Jan 10, 2013
27636287	Jan 10, 2013
39122673	Jan 11, 2013
39150663	Jan 11, 2013
42275701	Jan 11, 2013
39106517	Jan 11, 2013
41371309	Jan 16, 2013
42840603	Jan 20, 2013
42652691	Jan 20, 2013
42542235	Jan 21, 2013
41880955	Jan 21, 2013	Jan 21, 2013

I have below data in Excel file. I need to fill the empty cells in boundary column based on below logic when export data to qlikview.

Find is there a value in Boundary. Then fill all the above raws with boundary value until you have a value in boundary.

That means we have to fill first 5 raws with Jan 8, 2013 and other empties with Jan 21, 2013.

Is this possible.

Thank You

[Chanty4u]

hi,

https://community.qlik.com/message/147279#147279

chk dis

[Chanty4u]

this too..

Loading the data with empty cells

[anuradhaa]

It get top raw and fill below cells with that value. but i want to get bottom and fill top cells

[sunny_talwar]

Try this:

Table:

LOAD @1 as ID,

    @2 as Date,

    @3 as Boundary

FROM

[https://community.qlik.com/thread/199245]

(html, codepage is 1252, no labels, table is @1);

FinalTable:

NoConcatenate

LOAD ID,

  Date,

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

Resident Table

Order By Date desc, Boundary desc;

DROP Table Table;

[settu_periasamy]

Hi,

The another way (if is a small file), may be you can use Enable Transformation Tab

It will create the script like

LOAD ID,

     Date,

     Boundary

FROM

[..\Book1.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Replace(3, bottom, StrCnd(null))));

[sunny_talwar]

Settu‌ you mentioned that if this is a small file. What happens for a bigger database? Is this not a good way to fill data if you have, lets say 10+ million rows?

[anuradhaa]

Thanks Sunny,

What happen if i need to use attached excel, Use attached qvw also.

Thanks

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD If(Len(Trim(Boundary)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

  ID,

    Boundary

FROM

testPT.xls

(biff, embedded labels, table is Sheet1$);

FinalTable:

NoConcatenate

LOAD ID,

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

Resident Table

Order By Flag desc, Boundary;

DROP Table Table;

[settu_periasamy]

sunindia‌ i did a test after seeing your reply with my 1 lakh qvd data.. but laptop  got hanged.. I'm not sure about the performance..