Need to read Excel

[shannoypaul]

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.

[Anonymous]

may be helpful to you?

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

[sunny_talwar · Accepted solution]

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;

[shannoypaul]

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

Each year the file would be appended.

[sunny_talwar]

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

[shannoypaul]

I need to automate it across years .

Anyway, Thank you for the help .

[sunny_talwar]

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?

[shannoypaul]

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