Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alvinford
Contributor III
Contributor III

How to Achieve this

Hi ,

I have the data in the following format.

Data
Tuition and Other fees
10000
10100
10300
10400
Inter-charging rental
48000
Course/seminar fee
45000
Rent
41300
Interest Income
43100

I have to convert the data into the following format.

DataCode
Tuition and Other fees10000
Tuition and Other fees10100
Tuition and Other fees10300
Tuition and Other fees10400
Inter-charging rental48000
Course/seminar fee45000
Rent41300
Interest Income43100

Request your help to achieve this.

Please find the attached Excel

Regards,

Alvin

1 Solution

Accepted Solutions
jer_2011
Creator II
Creator II

Hola,

Espero te sirva este ejmplo:

//script

Tabla1:

Load Data,

          if(isnull(Peek('Data1'))=-1,Data,if(num(Data)>0,Peek('Data1'),if(len(Data)=2,Peek('Data1'),Data))) as Data1,

          if( num(Data)>0, Data, null()) as Code

FROM

LB01_20140930_223437.xls

(biff, embedded labels, table is Sheet1$);

NoConcatenate

tabla_final:

Load Data1 as Data,

        Code

Resident Tabla1

where Code <> null();

DROP Table Tabla1;

// te quedara de la siguiente forma

ejemplo 300914.png

saludos espero te sirva.

View solution in original post

2 Replies
jer_2011
Creator II
Creator II

Hola,

Espero te sirva este ejmplo:

//script

Tabla1:

Load Data,

          if(isnull(Peek('Data1'))=-1,Data,if(num(Data)>0,Peek('Data1'),if(len(Data)=2,Peek('Data1'),Data))) as Data1,

          if( num(Data)>0, Data, null()) as Code

FROM

LB01_20140930_223437.xls

(biff, embedded labels, table is Sheet1$);

NoConcatenate

tabla_final:

Load Data1 as Data,

        Code

Resident Tabla1

where Code <> null();

DROP Table Tabla1;

// te quedara de la siguiente forma

ejemplo 300914.png

saludos espero te sirva.

alvinford
Contributor III
Contributor III
Author

Dear Velasco,

Thank you for your reply.

Have a great day.

Regards,

Alvin.