Interesting QlikView data manipulation challenge

[#1]

Hi fellow Qlikview devlopers,

I have intersting data manipulation challenge. I have simple column where data is broken in the following manner (table 1)

Table1:

Column1

A

B

C

A

B

A

I have to take table 1 and insert a new row above everytime i see the value A. The position of this new inserted row is very important (table 2).

Table2:

Column1

A

B

C

A

B

A

Please advise.

Thanks for your time.

Regards

Thomas.

[Gysbert_Wassenaar · Accepted solution]

Something like this:

T1:

LOAD *, RowNo() as rowid INLINE [

    Column1

    A

    B

    C

    A

    B

    A

];

join

Load * inline [

Column1, Dummy

A, 1

A, 2

];

T2:

load if(Dummy=2,null(),Column1) as Column1, Dummy

Resident T1 Order by rowid, Dummy desc;

drop field Dummy;

drop table T1;

[#3]

Thanks a lot.