Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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.
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;
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.
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
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;
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.
Thanks Gysbert & Jagan
It works as required
Really appreciate it
A
Didnt realise that about the loop Jagan
Thanks
A