Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
eruditio
Contributor III

Transform data based on fixed values


Hi,

I have a table that I need to manipulate.

Table1:

Load * Inline [

Type, Value,

1, B,

2, B,

3, B,

33,B,

32,B,

31,B,

40,B,

41, B,

42, B,

45, B,

]  

I have another table which will serve as a basis for transforming T1.Value

Table2:

Load * Inline [

NewValue, Frequency,

K, 0.3

L, 0.3

M, 0.4

]

The New Table 3 should look like:

Table3:

Load * Inline [

Type, Value,

1, K,

2, K,

3, K,

33,L,

32,L,

31,L,

40,M,

41, M,

42, M,

45, M,

]

I am thinking of making selections based on record count, with the biggest value taking all remainder in order to avoid fractions.

Your thought on the matter is precious.

Kind regards,


Antoine

1 Solution

Accepted Solutions

Re: Transform data based on fixed values

Hope the attached file helps

Table1:

Load *, Rowno() AS Rows Inline [

Type, Value,

1, B,

2, B,

3, B,

33,B,

32,B,

31,B,

40,B,

41, B,

42, B,

45, B,

];

LET vRows = NoOfRows('Table1');

Table2:

Load * Inline [

NewValue, Frequency,

K, 0.3

L, 0.3

M, 0.4

];

Left Join(Table1)

LOAD

  NewValue,

  RowNo() AS Rows

While (IterNo()-1)/$(vRows) <= Frequency and RowNo()<$(vRows);

LOAD

  *

Resident Table2;

DROP Field Rows;

2 Replies

Re: Transform data based on fixed values

Hope the attached file helps

Table1:

Load *, Rowno() AS Rows Inline [

Type, Value,

1, B,

2, B,

3, B,

33,B,

32,B,

31,B,

40,B,

41, B,

42, B,

45, B,

];

LET vRows = NoOfRows('Table1');

Table2:

Load * Inline [

NewValue, Frequency,

K, 0.3

L, 0.3

M, 0.4

];

Left Join(Table1)

LOAD

  NewValue,

  RowNo() AS Rows

While (IterNo()-1)/$(vRows) <= Frequency and RowNo()<$(vRows);

LOAD

  *

Resident Table2;

DROP Field Rows;

eruditio
Contributor III

Re: Transform data based on fixed values

Many thanks Celambarasan.

I removed the join and got exactly what I needed.

This is great

Community Browser