Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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??