topic Re: Re: create a map using "Where" in QlikView

create a map using "Where"

Fri, 17 Jan 2014 08:35:22 GMT

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

My_Map:

MAPPING LOAD Process_instance, 'SME' as Flag

RESIDENT lENDERS

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

Re: create a map using "Where"

Peter_Cammaert — Fri, 17 Jan 2014 08:40:57 GMT

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:

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

Good luck,

Peter

Re: create a map using "Where"

Fri, 17 Jan 2014 09:00:35 GMT

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

Re: create a map using "Where"

jagan — Fri, 17 Jan 2014 09:11:16 GMT

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.

Re: Re: create a map using "Where"

Peter_Cammaert — Fri, 17 Jan 2014 09:36:56 GMT

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

Re: create a map using "Where"

Fri, 17 Jan 2014 09:49:45 GMT

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