topic Re: If Transaction date is the most recent in QlikView

If Transaction date is the most recent

Fri, 07 Mar 2014 13:04:27 GMT

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

Re: If Transaction date is the most recent

Gysbert_Wassenaar — Fri, 07 Mar 2014 13:07:22 GMT

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;

Re: If Transaction date is the most recent

jagan — Fri, 07 Mar 2014 13:11:31 GMT

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.

Re: If Transaction date is the most recent

Fri, 07 Mar 2014 13:30:47 GMT

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

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;

Re: Re: If Transaction date is the most recent

jagan — Fri, 07 Mar 2014 13:42:00 GMT

Hi,

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

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.

Re: If Transaction date is the most recent

Fri, 07 Mar 2014 13:50:20 GMT

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

Re: If Transaction date is the most recent

Gysbert_Wassenaar — Fri, 07 Mar 2014 13:54:57 GMT

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;

Re: If Transaction date is the most recent

jagan — Fri, 07 Mar 2014 13:58:12 GMT

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.

Re: Re: If Transaction date is the most recent

Fri, 07 Mar 2014 14:01:47 GMT

Thanks Gysbert & Jagan

It works as required

Really appreciate it

Re: Re: If Transaction date is the most recent

Fri, 07 Mar 2014 14:03:05 GMT

Didnt realise that about the loop Jagan

Thanks

Re: Re: If Transaction date is the most recent

sunilkumarqv — Fri, 07 Mar 2014 14:30:36 GMT

See attached Please

i did what you want

Re: Re: If Transaction date is the most recent

sunilkumarqv — Fri, 07 Mar 2014 14:31:13 GMT

try this

Ta:

LOAD * Inline

[

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

];

NoConcatenate

ta1:

LOAD SubField(Stamp,' ',1) as Date

Resident Ta;

Join

LOAD App,Type, max(Date) as Stamp ,'Yes' as High

Resident ta1;