If Transaction date is the most recent

[#1]

Hi Experts

I hope you can help,

I currently load numerous Excel files into my dashboard.

Once of the fields is a Date and Time field. I am looking to identify the rows of data where the date is the highest. So in example below where date is the 7 / Jan

App             Type          Stamp                            High   

1               Food          01/01/2014  15:15:00          No

1               Food          01/01/2014  18:15:00          No

1               Food          07/01/2014  15:15:00          Yes

1               Food          02/01/2014  15:15:00          No

1               Food          07/01/2014  18:15:00          Yes

1               Food          06/01/2014  15:15:00          No

I think it should be something like this but cant quite tweak it - any help really appreciated

if (max(date(date(Stamp))),"Yes","No") as High

Thanks

A

[Gysbert_Wassenaar]

You could of course simply add a listbox for the Date field and then select the maximum date. Your charts would then show the data related to the selected maximum date.

If you want a Yes/No flag then you can calculate it in the script:

TableX:

LOAD App, Type, Stamp

From ...somewhere...

join

LOAD max(Stamp) as Stamp, 'Yes' as High

Resident TableX;

That will give you the Yes values. If you also want No values you need an additional step

Result:

Noconcatenate

LOAD App, Type, Stamp, if(len(trim(High)),High,'No') as High

resident TableX;

Drop TableX;

[jagan]

Hi,

Data:

LOAD

*

FROM Data;

LEFT JOIN(Data)

LOAD

Max(Stamp) AS Stamp,

'Yes' AS High

RESIDENT Data;

FinalData:

NoConcatenate

LOAD

*,

If(High = 'Yes', High, No)

RESIDENT Data;

DROP TABLE Data;

Hope this helps you.

Regards,

jagan.

[#4]

Hi Guys

I cant get it to work - if  I post my code could you plaese advise how to slot it in please ?

This does excatly what I need so far - so it loads all excel files from a particular folder and then identifes which one belongs to a certain group

But each time I try and slot in the logic for the latest DATE in the Stamp , the load either fails or it only loads the latest date

Thanks

A

let path_Alles = '\vs2-alpfc2\Business Support\Business Supports\Load\*.xls; 
for each File in filelist (path_Alles)

Master:
LOAD
Process_instance as App,
Curr_Activity_Description as Type,
Curr_Txn_Date as Stamp,
State_Code,
Actioned_User_Id,
Prev_Activity_Description,
Prev_Txn_Date,
/*if(date(date(Curr_Txn_Date))=Max(date(date(Curr_Txn_Date))),'Yes','No') as High,*/
applymap('Team',Curr_Activity_Description,null()) as Team,
applymap('SubTeam',Curr_Activity_Description,null()) as SubTeam,
Date(date(Curr_Txn_Date)) as Date,
Date#( left(filename(),2)&mid(filename(),4,2)&mid(filename(),7,4), 'DDMMYYYY') as date2,
Filename() as File,
FileTime() AS DateFile

  
FROM
[\\vs2-alpfc2\Business Support\Business Supports\Load\*.xls]
(biff, embedded labels)
where State_Code ='RDY' or State_Code ='CMP';

My_Map:

MAPPING LOAD Process_instance, 'SME' as Flag
RESIDENT Master
where Prev_Activity_Description = 'NAPS Contact Customer' and State_Code='CMP' and date(date(Prev_Txn_Date)) > '01/12/2013' ;

FinTable:
LOAD *,
Applymap('My_Map', Process_instance, 'PL') as Base
RESIDENT Master;

DROP Table Master;

[jagan]

Hi,

Try this script, I think you don't need For loop.

Master:
LOAD
Process_instance as App,
Curr_Activity_Description as Type,
Curr_Txn_Date as Stamp,
State_Code,
Actioned_User_Id,
Prev_Activity_Description,
Prev_Txn_Date,
/*if(date(date(Curr_Txn_Date))=Max(date(date(Curr_Txn_Date))),'Yes','No') as High,*/
applymap('Team',Curr_Activity_Description,null()) as Team,
applymap('SubTeam',Curr_Activity_Description,null()) as SubTeam,
Date(date(Curr_Txn_Date)) as Date,
Date#( left(filename(),2)&mid(filename(),4,2)&mid(filename(),7,4), 'DDMMYYYY') as date2,
Filename() as File,
FileTime() AS DateFile

FROM
[\\vs2-alpfc2\Business Support\Business Supports\Load\*.xls]
(biff, embedded labels)
where State_Code ='RDY' or State_Code ='CMP';

LEFT JOIN(Master)

LOAD

Max(Stamp) AS Stamp,

'Yes' AS HighTemp

RESIDENT Master;

My_Map:

MAPPING LOAD Process_instance, 'SME' as Flag
RESIDENT Master
where Prev_Activity_Description = 'NAPS Contact Customer' and State_Code='CMP' and date(date(Prev_Txn_Date)) > '01/12/2013' ;

FinTable:
LOAD *,
Applymap('My_Map', Process_instance, 'PL') as Base,

If(HighTemp = 'Yes', HighTemp, 'No') AS High
RESIDENT Master;

DROP TABLE Master;

Hope this helps you.

Regards,

jagan.

[#6]

Hi Jagan

This is almost as I need - I kept the loop as I need to load numerous files from 1 folder.

But the code as is above makes the MAX TIMESTAMP as Yes - I am looking to mark the highest timestamp(S) based on the date

App             Type          Stamp                            High  

1               Food          01/01/2014  15:15:00          No

1               Food          01/01/2014  18:15:00          No

1               Food          07/01/2014  15:15:00          Yes

1               Food          02/01/2014  15:15:00          No

1               Food          07/01/2014  18:15:00          Yes

1               Food          06/01/2014  15:15:00          No

I think it should be something like this but cant quite tweak it - any help really appreciated

if (max(date(date(Stamp))),"Yes","No") as High

Thanks A

[Gysbert_Wassenaar · Accepted solution]

Use the floor function on the Stamp field to create a date only field:

TableX:

LOAD App, Type, Stamp, floor(Stamp) as Date

From ...somewhere...

join

LOAD max(Date) as Date, 'Yes' as High

Resident TableX;

[jagan]

Hi,

You can use Max() in Load statement without the Group by, check the Load Statement syntax for Group by.  Is the script which I given is working?

Without using For loop you can load many files in a single shot  by using *.xls.

Master:
LOAD
Process_instance as App,
Curr_Activity_Description as Type,

Date(Curr_Txn_Date) AS Date,
Curr_Txn_Date as Stamp,
State_Code,
Actioned_User_Id,
Prev_Activity_Description,
Prev_Txn_Date,
/*if(date(date(Curr_Txn_Date))=Max(date(date(Curr_Txn_Date))),'Yes','No') as High,*/
applymap('Team',Curr_Activity_Description,null()) as Team,
applymap('SubTeam',Curr_Activity_Description,null()) as SubTeam,
Date(date(Curr_Txn_Date)) as Date,
Date#( left(filename(),2)&mid(filename(),4,2)&mid(filename(),7,4), 'DDMMYYYY') as date2,
Filename() as File,
FileTime() AS DateFile

FROM
[\\vs2-alpfc2\Business Support\Business Supports\Load\*.xls]
(biff, embedded labels)
where State_Code ='RDY' or State_Code ='CMP';

LEFT JOIN(Master)

LOAD

Max(Date) AS Date,

'Yes' AS HighTemp

RESIDENT Master;

My_Map:

MAPPING LOAD Process_instance, 'SME' as Flag
RESIDENT Master
where Prev_Activity_Description = 'NAPS Contact Customer' and State_Code='CMP' and date(date(Prev_Txn_Date)) > '01/12/2013' ;

FinTable:
LOAD *,
Applymap('My_Map', Process_instance, 'PL') as Base,

If(HighTemp = 'Yes', HighTemp, 'No') AS High
RESIDENT Master;

DROP TABLE Master;

Hope this helps you.

Regards,

jagan.

[#9]

Thanks Gysbert & Jagan

It works as required

Really appreciate it

A

[#10]

Didnt realise that about the loop Jagan

Thanks

A