Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Partner
Partner

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;

Partner
Partner

Re: Transform data based on fixed values

Many thanks Celambarasan.

I removed the join and got exactly what I needed.

This is great