Grouping of rows from staright table

[varma8998]

Hi folks,

please help me with the below question if possible ASAP

I had table like this

Curr   scenario    code   amount

XXX    XXXXX     ABC   XXXXX

XXXX  XXXXX    CBA  XXXXXX

XXXX  XXXXX   BCA   XXXXXXX

XXXX XXXXX    FDE      XXXXX

and so on...…..

Here I want to group few codes as one code with different name

for example ABC + CBA as GHM

                   (ABC+CBA) - (BCA) as MHG

and so on...…….

and the out put I need like this

Curr   scenario    code   amount

XXX    XXXXX     ABC   XXXXX

XXXX  XXXXX    CBA  XXXXXX

XXXX  XXXXX   BCA   XXXXXXX

XXXX XXXXX    FDE      XXXXX

XXXX XXXXX    GHM XXXXXX

XXXX XXXXX    MHG XXXXX

Thanks & Regards,

Varma

[Simon_Astakhov]

Hi!

One of solutions is making mapping table with association of multiple values to single one.

For example:

1: AssociationTable: Load Code as CodeOriginal, 1 as Sign, Code RESIDENT 'OriginalTable';

2: Concatenate(AssociationTable) Load Code as CodeOriginal, 1 as Sign, 'GHM' as Code RESIDENT  'OriginalTable' WHERE Code = 'ABC' OR Code = 'CBA';

3: Concatenate(AssociationTable) Load Code as CodeOriginal, IF(Code='BCA',-1,1) as Sign, 'MHG' as Code RESIDENT  'OriginalTable' WHERE Code = 'ABC' OR Code = 'CBA';

After that you can calculate your values using sum() and multiply values with [Sign] field. For example:

Sum([amount] * [Sign]) //BCA values will sum with others, but with reversed Sign. Its like minus 😃

// dont forget to rename original field [Code] in old table to [CodeOriginal] for link.

//You can create this table via external application like excel to simplify your work.

[anushree1]

Can you share a sample input and desired output with some data for understanding as its not that clear what you are trying to achieve here. By the way are the values in code column numbers

[Simon_Astakhov]

Here is some pictures of solution with sample application:

Table structure:

And solution with calculation:

After that you can make any joins and groupBy if you need single or/and precalculated tables.