topic Re: Excel Issue in QlikView

Excel Issue

sat_tok52 — Tue, 12 May 2015 07:14:12 GMT

Hi All,

I am facing the issue with excel .In excel there is column called Org Grp ,in this column have a row Application etc. when selecting application it should show related association .My result Should be like below Excel .But in qlikview not showing like below.Please any help.

Regards,

Sateesh

Re: Excel Issue

datanibbler — Tue, 12 May 2015 07:23:01 GMT

Hi Sateesh,

that is most probably because in your Excel, a nr. of rows in column B are merged while in the following columns they are not - so when loading the file into QlikView, the contents of column B are associated only with the topmost of the rows in column C.

There should be a possibility to fill those values in the data_load_wizard in QlikView. I'm just not sure exactly how.

I hope someone here can help you.

Best regards,

DataNibbler

Re: Excel Issue

jagan — Tue, 12 May 2015 07:30:44 GMT

Hi,

Try like this

LOAD

If(Len(Trim(OrgGroup)) = 0, Peek('OrgGroupNew'), OrgGroup) AS OrgGroupNew,

If(Len(Trim(ConsolidatedGroup)) = 0, Peek('ConsolidatedGroupNew'), ConsolidatedGroup) AS ConsolidatedGroupNew

From ExcelFileName;

Hope this helps you..

Regards,

Jagan.

Re: Excel Issue

avinashelite — Tue, 12 May 2015 07:31:59 GMT

Hi Sateesh,

It's the normal way how qlikview loads the excel, to load according to your requirement we need to make the script the accordingly. Can you please share the sample data and app , so that we can try and build the script for the same.

Re: Excel Issue

robert_mika — Tue, 12 May 2015 07:45:50 GMT

DataNibbler thanks for the "Light bulb"

(Based on example below)

When you are loading your data go to

Table Files-Next-Transformation Step

Go to "Cell Condition" choose "Empty",Leave Fill Type as "Above"

Do this for each column

Final result:

Re: Excel Issue

mrossoit — Tue, 12 May 2015 07:49:23 GMT

Hi,

in your script editor:

1 - Click on Table Files

2 - Select the xls file you desire to load

3 - next

4 - enable transformation steps

5 - go to fill tab

6 - click on the number on the top of the column you want to fill

7 - click on fill... button

8 - cell condition -> ok -> ok

9 - repeat steps 6, 7, 8 for every column you want to fill

10 - next -> finish

Hope it helps

Regards

Re: Excel Issue

sat_tok52 — Tue, 12 May 2015 07:57:47 GMT

Thanks Robert it is working fine

Re: Excel Issue

jagan — Tue, 12 May 2015 08:10:46 GMT

Hi,

Is this not working, this can be done using Qlikview scripting using Peek()

LOAD

If(Len(Trim(OrgGroup)) = 0, Peek('OrgGroupNew'), OrgGroup) AS OrgGroupNew,

If(Len(Trim(ConsolidatedGroup)) = 0, Peek('ConsolidatedGroupNew'), ConsolidatedGroup) ASConsolidatedGroupNew

From ExcelFileName;

Regards,

Jagan.

Re: Excel Issue

sat_tok52 — Tue, 12 May 2015 09:35:12 GMT

Hi Robert,

Thanks Robert it is working fine,but Column Name coming in list

Regards,

Sateesh

Re: Excel Issue

sat_tok52 — Tue, 12 May 2015 09:57:14 GMT

Hi Rosso,

I fallow same navigation what you mentioned.But column header name coming in list box

-Sateesh.

Re: Excel Issue

sat_tok52 — Tue, 12 May 2015 10:00:14 GMT

Hi Robert,

Thanks Robert it is working fine,but Column header Name coming in list box.

-Sateesh

Re: Excel Issue

robert_mika — Tue, 12 May 2015 10:11:54 GMT

You need to choose "Embedded Labels"

Re: Excel Issue

mrossoit — Tue, 12 May 2015 10:27:49 GMT

At point 2 of my guide, on Header Size box select Lines and write 3 on the other box on the left.

Your xls has a 3 lines header.

After you clicked finish you'll mast redefine field names with correct headers.

Regards

Re: Excel Issue

mrossoit — Tue, 12 May 2015 10:35:03 GMT

Regards,