Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted

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

View solution in original post

7 Replies
Highlighted
Champion
Champion

Re: Need to read Excel

Highlighted

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

View solution in original post

Highlighted
Creator
Creator

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.

Highlighted

Re: Need to read Excel

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

Highlighted
Creator
Creator

Re: Need to read Excel

I need to automate it across years .

Anyway, Thank you for the help .

Highlighted

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?

Highlighted
Creator
Creator

Re: Need to read Excel

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