topic Re: Need to read Excel in QlikView

Need to read Excel

shannoypaul — Thu, 25 Feb 2016 12:22:58 GMT

Hello,

Please find an excel attached.

The first Sheet 1 (Source) is the file I get. I need to transform the file in such a way that I get the output as shown in Sheet 2 (Output).

Thanks in Advance.

Re: Need to read Excel

Anonymous — Thu, 25 Feb 2016 12:32:30 GMT

may be helpful to you?

QlikView Load Multiline Header Labels as Field Names from Excel (or CSV) source · GitHub

Re: Need to read Excel

sunny_talwar — Thu, 25 Feb 2016 13:19:35 GMT

Try this:

Table:

LOAD RowNo() as Sort,

Ceil(RowNo()/4) as CountryNum,

F1 as [Importing FCI Site],

F2 as [Supplying Region],

F3 as [Inventory Type],

[Final Adder (%)1]

FROM

[Upload - Markup.xls]

(biff, embedded labels, header is 2 lines, table is Source$, filters(

Replace(2, top, StrCnd(null))

));

FinalTable:

LOAD Sort,

If(Len(Trim([Importing FCI Site])) = 0, Peek('Importing FCI Site'), [Importing FCI Site]) as [Importing FCI Site],

[Supplying Region],

[Inventory Type],

[Final Adder (%)1]

Resident Table

Order By CountryNum, [Importing FCI Site] desc;

DROP Table Table;

Re: Need to read Excel

shannoypaul — Fri, 26 Feb 2016 12:46:19 GMT

Sunny, Next Year Final adder % 2017 would be [Final Adder%2]. How can I resolve that issue?

Each year the file would be appended.

Re: Need to read Excel

sunny_talwar — Fri, 26 Feb 2016 12:48:29 GMT

So what is the problem with that? You can add that as another column

Re: Need to read Excel

shannoypaul — Mon, 29 Feb 2016 05:32:05 GMT

I need to automate it across years .

Anyway, Thank you for the help .

Re: Need to read Excel

sunny_talwar — Mon, 29 Feb 2016 09:34:28 GMT

Automated in what way? If a new period is added you would like QlikView to read the new year without you needing to go in an add a new year? Is that what the goal is?

Re: Need to read Excel

shannoypaul — Wed, 02 Mar 2016 10:44:12 GMT

Yes, somehow if we could read the most recent Column for the Final Adder %.