topic Re: Fill column based when exporting data from excel in QlikView

Fill column based when exporting data from excel

anuradhaa — Tue, 12 Jan 2016 12:35:46 GMT

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

Re: Fill column based when exporting data from excel

Chanty4u — Tue, 12 Jan 2016 12:39:49 GMT

hi,

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

chk dis

Re: Fill column based when exporting data from excel

Chanty4u — Tue, 12 Jan 2016 12:40:20 GMT

this too..

Loading the data with empty cells

Re: Fill column based when exporting data from excel

anuradhaa — Tue, 12 Jan 2016 12:51:07 GMT

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

Re: Fill column based when exporting data from excel

sunny_talwar — Tue, 12 Jan 2016 12:54:12 GMT

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;

Re: Fill column based when exporting data from excel

settu_periasamy — Tue, 12 Jan 2016 13:13:21 GMT

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

Re: Fill column based when exporting data from excel

sunny_talwar — Tue, 12 Jan 2016 13:18:44 GMT

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?

Re: Fill column based when exporting data from excel

anuradhaa — Tue, 12 Jan 2016 14:09:36 GMT

Thanks Sunny,

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

Thanks

Re: Fill column based when exporting data from excel

sunny_talwar — Tue, 12 Jan 2016 14:23:55 GMT

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;

Re: Fill column based when exporting data from excel

settu_periasamy — Tue, 12 Jan 2016 14:39:27 GMT

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..

Re: Fill column based when exporting data from excel

sunny_talwar — Tue, 12 Jan 2016 14:47:43 GMT

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

Re: Fill column based when exporting data from excel

settu_periasamy — Tue, 12 Jan 2016 15:27:33 GMT

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

Re: Fill column based when exporting data from excel

anuradhaa — Mon, 18 Jan 2016 18:22:06 GMT

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.

Re: Fill column based when exporting data from excel

sunny_talwar — Mon, 18 Jan 2016 18:28:00 GMT

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

Re: Fill column based when exporting data from excel

anuradhaa — Tue, 19 Jan 2016 03:31:55 GMT

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;

Re: Fill column based when exporting data from excel

anuradhaa — Tue, 19 Jan 2016 03:34:20 GMT

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.

Re: Fill column based when exporting data from excel

anuradhaa — Tue, 19 Jan 2016 05:34:05 GMT

I think issue is with the Concatenate