How to rearrange excel file into Qlikview

[Anonymous]

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

[sasiparupudi1]

pl attach the sample excel file

[isingh30]

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

Thank you!

[Anonymous]

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

[Anonymous]

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

[kamielrajaram · Accepted solution]

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