Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks ,
Please see below this script:
Fact_new:
NoConcatenate
load
*,
dual(if(TicketAge >=0 and TicketAge <= 7, '0-7 days',
if(TicketAge >=8 and TicketAge <= 14, '8-14 days',
if(TicketAge >=15 and TicketAge <= 21, '15-21 days',
if(TicketAge >=22 and TicketAge <= 28, '22-28 days',
if(TicketAge >=29 and TicketAge <= 60, '29-60 days',
if(TicketAge >=61 and TicketAge <= 90, '61-90 days',
if(TicketAge >=91 and TicketAge <= 120, '91-120 days',
if(TicketAge >121, 'Above 121 days')))))))),
if(TicketAge >=0 and TicketAge <= 7, 10,
if(TicketAge >=7 and TicketAge <= 14, 20,
if(TicketAge >=15 and TicketAge <= 21, 30,
if(TicketAge >=22 and TicketAge <= 28, 40,
if(TicketAge >=29 and TicketAge <= 60, 50,
if(TicketAge >=61 and TicketAge <= 90, 60,
if(TicketAge >=91 and TicketAge <= 120, 70,
if(TicketAge >121, 80))))))))
) as TicketAgeGroup;
load
*,
%OpenedDate as %DateID,
//if(isnull(%ClosedDate), %OpenedDate, %ClosedDate) as %DateID,
// if(isnull(%ClosedDate), 1, 0) as isNew,
if(not isnull(%OpenedDate), 1, 0) as isOpened,
// if(not isnull(%ClosedDate), 1, 0) as isClosed,
floor(today()-%OpenedTime) as TicketAge,
if(TicketPriority = 1, 1, 0) as isPrio1,
if(TicketPriority = 2, 1, 0) as isPrio2,
if(TicketPriority = 3, 1, 0) as isPrio3,
if(TicketPriority = 4, 1, 0) as isPrio4,
if(TicketPriority = 5, 1, 0) as isPrio5,
RowNo() as Rowno,
applymap('map_Urgency', Urgency) as Urgency2
Resident Fact
where not isnull(%OpenedDate);
//Concatenate
//load
// *,
// //if(isnull(%ClosedDate), %OpenedDate, %ClosedDate) as %DateID,
// %ClosedDate as %DateID,
//// if(not isnull(%ClosedDate), 1, 0) as isClosed
////Resident Fact
////where not isnull(%OpenedDate);
drop table Fact;
rename table Fact_new to Fact;
drop field Urgency;
RENAME field Urgency2 to Urgency;
My requirement: There is one more filed as %ClosedDate which I want to call as %DateID, so that %DateID should include data of both (%ClosedDate + %OpenedDate)
Please help me how to do this ., already in script I'm having this %OpenedDate as %DateID, but I want to call %ClosedDate also as %DateID
Thanks,
AS
Folks please see the attachment.
Thanks,
AS
Fact_new:
NoConcatenate
load
*,
dual(if(TicketAge >=0 and TicketAge <= 7, '0-7 days',
if(TicketAge >=8 and TicketAge <= 14, '8-14 days',
if(TicketAge >=15 and TicketAge <= 21, '15-21 days',
if(TicketAge >=22 and TicketAge <= 28, '22-28 days',
if(TicketAge >=29 and TicketAge <= 60, '29-60 days',
if(TicketAge >=61 and TicketAge <= 90, '61-90 days',
if(TicketAge >=91 and TicketAge <= 120, '91-120 days',
if(TicketAge >121, 'Above 121 days')))))))),
if(TicketAge >=0 and TicketAge <= 7, 10,
if(TicketAge >=7 and TicketAge <= 14, 20,
if(TicketAge >=15 and TicketAge <= 21, 30,
if(TicketAge >=22 and TicketAge <= 28, 40,
if(TicketAge >=29 and TicketAge <= 60, 50,
if(TicketAge >=61 and TicketAge <= 90, 60,
if(TicketAge >=91 and TicketAge <= 120, 70,
if(TicketAge >121, 80))))))))
) as TicketAgeGroup;
load
*,
%OpenedDate as %DateID,
//if(isnull(%ClosedDate), %OpenedDate, %ClosedDate) as %DateID,
// if(isnull(%ClosedDate), 1, 0) as isNew,
if(not isnull(%OpenedDate), 1, 0) as isOpened,
// if(not isnull(%ClosedDate), 1, 0) as isClosed,
floor(today()-%OpenedTime) as TicketAge,
if(TicketPriority = 1, 1, 0) as isPrio1,
if(TicketPriority = 2, 1, 0) as isPrio2,
if(TicketPriority = 3, 1, 0) as isPrio3,
if(TicketPriority = 4, 1, 0) as isPrio4,
if(TicketPriority = 5, 1, 0) as isPrio5,
RowNo() as Rowno,
applymap('map_Urgency', Urgency) as Urgency2
Resident Fact
where not isnull(%OpenedDate);
// Please help me here how to implement %ClosedDate as %DateID with concatenate load???????
Concatenate
load
*,
//if(isnull(%ClosedDate), %OpenedDate, %ClosedDate) as %DateID,
%ClosedDate as %DateID
//// if(not isnull(%ClosedDate), 1, 0) as isClosed
Resident Fact
where not isnull(%OpenedDate);
drop table Fact;
rename table Fact_new to Fact;
drop field Urgency;
RENAME field Urgency2 to Urgency;
What do you by %ClosedDate + %OpenedDate??