topic Re: Script Help????? in QlikView

Script Help?????

amit_saini — Tue, 08 Apr 2014 06:00:22 GMT

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,

Re: Script Help?????

amit_saini — Tue, 08 Apr 2014 06:20:32 GMT

Folks please see the attachment.

Thanks,

Re: Script Help?????

renjithpl — Tue, 08 Apr 2014 12:35:39 GMT

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;

Re: Script Help?????

Tue, 08 Apr 2014 22:12:55 GMT

What do you by %ClosedDate + %OpenedDate??