Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

shannoypaul
Contributor

Need to read Excel

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.

1 Solution

Accepted Solutions

Re: Need to read Excel

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;


Capture.PNG

7 Replies
balrajahlawat
Esteemed Contributor

Re: Need to read Excel

Re: Need to read Excel

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;


Capture.PNG

shannoypaul
Contributor

Re: Need to read Excel

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

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

shannoypaul
Contributor

Re: Need to read Excel

I need to automate it across years .

Anyway, Thank you for the help .

Re: Need to read Excel

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
Contributor

Re: Need to read Excel

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

Community Browser