Replace numbers with name in pivot table

[Heny]

Maybe this is a common question and very simple issue, but I am not successful in getting it right.

I have a table with work status. The status field in my table is a numeric sequence - 1,2,3,4,5

when shown on pivot table I will see work progress according to the numbers above

how can I replace the numbers with a value throughout my whole model?

as in 1 = "preparation"

2 = "documentation"

3 = "processing"

and so on...

is this done with inline table?

if yes, how does the inline table relate to my status field?

thanks all for your help!

[Adam_Romanowski]

You can use Maps:

Map_Status_Description:

mapping load * inline [

status_code, description

1, preparation

2, documentation

3, processing

];

then in script, use ApplyMap():

LOAD

...

ApplyMap('Map_Status_Description', field_with_status_code, 'not available') as "Status"

...

This code will replace status codes with description of status. It's always better to perform all possible calculations and data modelling in script.

[Or]

Assuming it's just the five, you're probably better off hard-coding it, e.g.

Match(Pick(Sequence,1,2,3,4,5),'preparation', 'documentation' etc)

You'll likely want to use this in a Dual() to ensure the order stays the same:

Dual(Match(Pick(Sequence,1,2),'preparation', 'documentation'),Sequence) as mySequence

[Padma123]

Pick(Match(id,1,2,3),'Preparation','documentation','proccessing')

[diegozecchini]

Hi!

above you have many solutions.

As alternative: If you don’t want to modify the script or create a new field, you can use an inline replacement in the pivot table dimension itself:

=IF(Status = 1, 'Preparation',
IF(Status = 2, 'Documentation',
IF(Status = 3, 'Processing',
IF(Status = 4, 'Review',
IF(Status = 5, 'Completed', 'Unknown')))))
This method is not recommended for larger datasets because it’s less efficient.

[Heny]

Thanks all for your help!

I'll see what works best

[diegozecchini]

Let us know what worked better, regards, Diego

[Heny]

inline with mapping worked great

thanks everyone!