create a map using "Where"

[#1]

Hi Experts

I'm looking to create a conditional map from my lENDERS table:

The logic I need to apply is :

If  Prev_Activity_Description = '123 Customer' AND its the First (Earliest value transaction date) [Pre_Txn_Date] then load the field Process_Instance with SME as the flag.

I'll then use this map in a later table

Any guidence really appreciated - I've tried the below but Im getting a pop up error - execution of script failed

Thanks

A

My_Map:

MAPPING LOAD Process_instance, 'SME' as Flag

RESIDENT lENDERS

where Prev_Activity_Description = '123 Customer' and min(Pre_Txn_Date)

[Peter_Cammaert]

the min()-part in your MAPPING LOAD is wrong. Min() is an aggregation function and needs a GROUP BY clause.

Use a two-step approach:

1. Reduce your table to only those transactions with the earliest date. Use GROUP BY to order them by transaciton ID or something.
2. Create a mapping table by copying the previous one and filtering out those that do not fit the '123 Customer' requirement.

Good luck,

Peter

[#3]

Hi Peter

I know how to create a table by loading from my existing table lENDERS , but I'm not sure how to write the code in order to reduce it by earliest transaction.

This is what I would need :

Load

Process_Instance,

Previous_Activity_Description as [PreAct]                         ( because I need to change the name to prevent syn keys)

when [Pre_Txn_Date] is the earliest one across all instances.

Can you help with the actual coding ? I beieve the logic is sound.

Thanks a mill

A

[jagan]

MinDate:

LOAD

min(Pre_Txn_Date) AS MinTxnDate

RESIDENT lENDERS;

//Get and store Minimum date

LET vDateMin = FieldValue('MinTxnDate', 1);

DROP TABLE MinDate;

My_Map:

MAPPING LOAD Process_instance, 'SME' as Flag

RESIDENT lENDERS

where Prev_Activity_Description = '123 Customer' and Pre_Txn_Date = Date('$(vDateMin)');

Hope this helps you.

Regards,

Jagan.

[Peter_Cammaert · Accepted solution]

How about this? Note that your situation may be slightly different. The attachment only explains a method to get the oldest instances using GROUP BY.

Good luck,

Peter

[#6]

Thanks a mill guys

I'm just going through it now as I am getting expected results. I'll let you know if I get it working

Thanks again for your help
A