topic Re: Transform data based on fixed values in QlikView

Transform data based on fixed values

Anonymous — Wed, 18 Sep 2013 19:26:03 GMT

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

Re: Transform data based on fixed values

CELAMBARASAN — Thu, 19 Sep 2013 01:06:36 GMT

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;

Re: Transform data based on fixed values

Anonymous — Thu, 19 Sep 2013 07:19:16 GMT

Many thanks Celambarasan.

I removed the join and got exactly what I needed.

This is great