Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_saini
Master III
Master III

Script Help?????

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

3 Replies
amit_saini
Master III
Master III
Author

Folks please see the attachment.

Thanks,

AS

renjithpl
Specialist
Specialist

 

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;

Not applicable

What do you by %ClosedDate + %OpenedDate??