Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

alvinford
New 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

Tags (1)
1 Solution

Accepted Solutions
jer_2011
Contributor II

Re: How to Achieve this

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.

2 Replies
jer_2011
Contributor II

Re: How to Achieve this

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
New Contributor III

Re: How to Achieve this

Dear Velasco,

Thank you for your reply.

Have a great day.

Regards,

Alvin.

Community Browser