The best practice to change/replace

[MindaugasBacius]

Hei,

LOAD * INLINE [
    Domain, Sale
    A, D
    A, G
    B, Y
    B, H
    C, Y
];

If I would like to change Y to X in case Domain is C, what would be the script?

That is the best practice knowing that the database is huge.

Thank you!

[oknotsen · Accepted solution]

If it is just 1 value you want to change and the actual load is not an InLine table, use an If statement like this:

if(Domain = 'C', 'X', Scale) as Scale,

If you have a lot of conversions, create mapping table and do it via an applyMap()

[marcus_sommer]

If you have only this single case an if-loop with: if(Domain = 'C' and Sale = 'Y', 'X', Sale) as Sale. Are there more (complex) cases pick(match()) or a mapping are easier to handle and more performant.

- Marcus

[MindaugasBacius]

You are right. I do need to replace the Sale value only when Domain is C. It's a single case.

I wasn't sure if IF clause is the most efficient way to do that. But if pros are saying so

[MindaugasBacius]

I am still thinking about this situation.

Let's say my case is little bit different. I need to change Sale Y to X if Domain is B and C. Would it be the same solution IF statement?

[marcus_sommer]

Even with this I would use a small if-statement and only if I experience serious performance-issues I would check if another method is more performant. You could try (enable logging for this) various approaches like a mapping or the if-loop but I doubt that you will see significantely differences even with larger datasets.

If you fetched the data from a database and transformed them within a preceeding load it's more unlikely to see differences between the bottleneck is rather the database respectively the network.

- Marcus