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

[sunny_talwar]

Okay, I would have not thought that either of them would have any performance difference. But I guess that is something which we might still need to test.

Everything else being same, I would prefer using the Peek()/Previous() method because I have a better understanding of the process. But if there are performance benefits, I don't mind switching to the other one

[settu_periasamy]

I will also do test.. and have to search, is this already discussed in community..:-)

[anuradhaa]

Hi,

Today i have faced strange thing.

When i load data based on your syntax it works well.

Today i have added several date filters and now it's not working correctly. (it fills data based on upper value) i have revert the change but nothing happen. i don't know why.

[sunny_talwar]

What filters did you add? Can you share a sample?

[anuradhaa]

Hi,

I have several xsl files and i need to load them separately. that boundary filter has to be filled seperately in each xsl based on above logic. after loading all scripts , i need to filter based on date. so i use below syntax,

Temp_Table1:

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

  ID,

    Boundary

FROM

testPT1.xls

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

Table1:

NoConcatenate

LOAD ID,

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

Resident Temp_Table1

Order By Flag desc, Boundary;

DROP Table Temp_Table1;

Temp_Table2:

NoConcatenate

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

  ID,

    Boundary

FROM

testPT2.xls

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

Table2:

NoConcatenate

LOAD ID,

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

Resident Temp_Table1

Order By Flag desc, Boundary;

DROP Table Temp_Table2;

[T_temp]:

NoConcatenate

Load * Resident Table1;

Concatenate

Load * Resident Table2;

DROP Table Table1;

DROP Table Table2;

[Final]:

NoConcatenate

Load *

Resident T_temp

Where (Boundary > MakeDate(2016, 1, 1));

DROP Table T_temp;

[anuradhaa]

one more thing

  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, 2016
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	Jan 21, 2016
42652691	Jan 20, 2013
42542235	Jan 21, 2013
41880955	Jan 21, 2013

need to avoid putting boundary values for the date don't have lower bound. in this case 42652691,42542235,41880955 needs to be empty.

[anuradhaa]

I think issue is with the Concatenate