How to Replace Nested If Statements in Load Editor?

[Delestia]

Hello Experts,

I'm looking to clean up load scripts with the aims to speed up application loading. I have already moved to a Star schema and also an ETL 3-Tier app system. We have moved from minutes to now under 30 seconds for the final user application.

I am stuck on how to process some of these nasty nested if statements in the data load on my transform layer and looking for creative solutions from the community. For the example below it is a nested if statement on the same field repetitively to bucket the aged inventory into larger groups for a user bar chart dimension. Is there any way to reduce the noise by maybe using a mapped load or some other join statement for this type of bucketing? My larger questions would be ultimately how to get as few nested If statements into the load editor as possible while maintaining functionality.

[Nested If Statement]
LOAD *,
If([Item Age] < 110, Dual('[, 110)', 1),If([Item Age] >= 110 and [Item Age] < 220, Dual('[110, 220)', 2),If([Item Age] >= 220 and [Item Age] < 330, Dual('[220, 330)', 3),If([Item Age] >= 330 and [Item Age] < 440, Dual('[330, 440)', 4),If([Item Age] >= 440, Dual('[440, )', 5)))))) AS [Item Age (Bucketed)]
FROM SOURCE_QVD

[Kushal_Chawda]

@Delestia  I would suggest to move this calculation in SQL while pulling the data from source. This way you will have buckets pre-calculated while creating base QBD itself. 

[ArnadoSandoval]

Hi @Delestia 

I will give you a different approach to the solution proposed by @Kushal_Chawda, mine work within the script, so there is no need to modify the SQL queries; my solution is based on numeric series; I do not know the performance impact you will get; it is worth the try measuring it.

Theory:

Your age-buckets are numeric series; each bucket has a minimum and a maximum age range; these buckets are separated by 110 unit of times (probably days or could be hours, it does not matter); the generic formula for the minimum age on each bucket is:

[Min Age] = 110 * [Bucket-Number] - 110

The formula for the maximum age on each bucket is:

[Max Age] = 110 * [Bucket-Number]

You can test these formulas to confirm their accuracy! Now there is a third formula with these series, the one to calculate the [Bucket-Number] given an [Item Age], which is this:

[Bucket-Number] = Floor(([Item Age] + 110) / 110 )

The screenshot below are the results of testing these formulas on Excel, the attached QVF contains a solution illustrating them.

Please share with us the performance improvement if you implement this logic!

[Gabriel]

@Delestia ,

Can I ask you to try using mapping table below on Age field.

Load the mapping table first

MapAgeBucket:
MAPPING
LOAD
Start + ITERNO()-1 AS MapFrom,
Group AS MapTo
INLINE [
Start, End, Group
0, 110, 0 - 110
111, 220, 111 - 220
221, 330, 221 - 330
331, 440, 331 - 440
441, 550, 441 - 550
551 ,1000, '>551'
]
WHILE Start+ ITERNO()-1 <= End;

In the script

LOAD .,.,

APPLYMAP('MapAgeBucket',AgeFieldName,'N/A')       AS [Age Bucket]

FROM ......;