Transform data based on fixed values

[Anonymous]

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

[CELAMBARASAN · Accepted solution]

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;

[Anonymous]

Many thanks Celambarasan.

I removed the join and got exactly what I needed.

This is great