Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor

How to rearrange excel file into Qlikview

Hi guys, would be wonderful if someone can help me out! Thanks in advance.

Excel File view:

              A                            B

1            File                        Incremental Data

2            Date                      9-5-2018

3

4           Client Number        Status

5           1                            Not Started

6           2                            Started

7           3                            Completed

8           4                            Started

9           5                            Not Started

10        6                            Completed 

Ideal result in Qlikview:

File                          Date                  Client Number      Status

Incremental Data     9-5-2018             1                            Not Started

Incremental Data     9-5-2018             2                            Started

Incremental Data     9-5-2018             3                            Completed

Incremental Data     9-5-2018             4                            Started

Incremental Data     9-5-2018             5                            Not Started

Incremental Data     9-5-2018             6                            Completed

1 Solution

Accepted Solutions
Highlighted
Contributor III

Re: How to rearrange excel file into Qlikview

Hi,

Not sure if this is what you require.Try this in the load

LOAD File,

     Date(Floor(Date(Date))) as Date

FROM

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 3), Pos(Top, 10), Select(3, 0))),

Transpose()

));

join

LOAD Num([Client Number]) as [Client Number],

     Status

FROM

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

Kind Regards

Kamiel

View solution in original post

5 Replies
Highlighted
Honored Contributor III

Re: How to rearrange excel file into Qlikview

pl attach the sample excel file

Highlighted
Valued Contributor

Re: How to rearrange excel file into Qlikview

Your data is not clear. Please attach sample application or data in excel sheet

Thank you!

Highlighted
New Contributor

Re: How to rearrange excel file into Qlikview

thanks for your reply, i've added a sample(see attachment).

Highlighted
New Contributor

Re: How to rearrange excel file into Qlikview

thanks for your reply, i've added a sample(see attachment).

Highlighted
Contributor III

Re: How to rearrange excel file into Qlikview

Hi,

Not sure if this is what you require.Try this in the load

LOAD File,

     Date(Floor(Date(Date))) as Date

FROM

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 3), Pos(Top, 10), Select(3, 0))),

Transpose()

));

join

LOAD Num([Client Number]) as [Client Number],

     Status

FROM

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

Kind Regards

Kamiel

View solution in original post