topic Re: Script Question in QlikView

Script Question

bnelson111 — Thu, 07 Mar 2013 21:54:23 GMT

I have a ODBC link importing data, all okay. Im getting the following results with Itemcode as a primary key, is there any way to calculate via script the days between Plate ordered and Received? they are appearing in different recordsets? See the script below. maybe this cant be done. Thanks in advance.

ODBC CONNECT TO visionII (XUserId is *****, XPassword is ****);
//-------- Start Multiple Select Statements ------

load

month(ApprovalDate) as [Month Approved],
Year(ApprovalDate) as [Year Approved],
WeekDay(ApprovalDate) as [Day Approved],

// Uses wildcard to create Item Type, for Trials and New Items and Finger Prints
//if(WildMatch((Comments),'Automatically created on Item entry'),'New Items') as [New Items],

If (WildMatch(ItemCode,'f*'),'Finger Prints',
if(WildMatch((Comments),'Automatically created on Item entry'),'New Items',

If (WildMatch(ItemCode,'t*'),'Trials','Not Trials')))
as [Item Type],

//Fault Code Description Count
//P1 Plate ripped while being stripped.
//P2 Plate worn.
//P3 Plate damaged in storage.
//P4 Plate damaged in press.
//P5 Plate damaged in washer.
//P6 Plate arrived from supplier damaged.
//P7 Plate creased.
//P8 Plate cracked.
//P9 Plate lost.

If(WildMatch(Ref1,'*p1*'),'Ripped Stripped',
if(WildMatch((Ref1),'*p2*'),'Worn',
if(WildMatch((Ref1),'*p4*'),'Damaged Press',
if(WildMatch((Ref1),'*p5*'),'Damaged Washer',
if(WildMatch((Ref1),'*p6*'),'Supplier Damaged',
if(WildMatch((Ref1),'*p7*'),'Creased',
if(WildMatch((Ref1),'*p8*'),'Cracked',
if(WildMatch((Ref1),'*p9*'),'Lost',
if(WildMatch((Ref1),'*p3*'),'Damage Storage',
If(WildMatch((Ref2),'*p1*'),'Ripped Stripped',
if(WildMatch((Ref2),'*p2*'),'Worn',
if(WildMatch((Ref2),'*p4*'),'Damaged Press',
if(WildMatch((Ref2),'*p5*'),'Damaged Washer',
if(WildMatch((Ref2),'*p6*'),'Supplier Damaged',
if(WildMatch((Ref2),'*p7*'),'Creased',
if(WildMatch((Ref2),'*p8*'),'Cracked',
if(WildMatch((Ref2),'*p9*'),'Lost',
If (WildMatch(Ref2,'*p3*'),'Damage Storage'))))))))))))))))))
as [Defect Code],

//If (WildMatch(Ref2,'*p3*'),'Damage Storage', 'No Code'))))))))))))))))))

ApproTimeSecs,
    ApprovalDate,



    if(Comments='Automatically created on Item entry',(ApprovalDate)) as [Day New Plate Ordered],

   if(StatusNum='99',(ApprovalDate)) as [Day New Plate Received],

// date( [Day New Plate Ordered], 'DD/MM/YYYY' ),
// date( [Day New Plate Received], 'DD/MM/YYYY' ),

    ApprovalOperator,
    ApprovalTime,
    Comments,
    CompNum,
    ItemCode,
    LastUserCode,
    LevelNum,
    PSAapproved,
    PSAextracted,
    Ref1,
    Ref2,
    StatusNum,
    TableRecId;

SQL SELECT ApproTimeSecs,
    ApprovalDate,
    ApprovalOperator,
    ApprovalTime,
    Comments,
    CompNum,
    ItemCode,
    LastUserCode,
    LevelNum,
    PSAapproved,
    PSAextracted,
    Ref1,
    Ref2,
    StatusNum,
    TableRecId
FROM PUB."PV_ItemApproval";
//-------- End Multiple Select Statements ------

Re: Script Question

CELAMBARASAN — Thu, 07 Mar 2013 23:52:19 GMT

Add the follwoing script to calculate days between recieved and ordered.

Difference:

Load

ItemCode,

Max([Day New Plate Received]) - Max([Day New Plate Ordered]) AS NumberOfDays

Resident TableName //table name of your script

GroupBy ItemCode;

Hope it helps

Re: Script Question

Fri, 08 Mar 2013 05:48:37 GMT

Hi ,

Please find the attached file for your solution.

Re: Script Question

bnelson111 — Fri, 08 Mar 2013 09:00:11 GMT

Thanks for the replies, I must be doing something wrong, See below:

Resident TableName Start: Is underlined in Red and also Groupby ItemCode;

Load script is named Start. Maybe its where im placing this Load instruction? Should it be after Start script?

Difference:

Load

ItemCode,

Max([Day New Plate Received]) - Max([Day New Plate Ordered]) AS NumberOfDays

Resident TableName Start:
//table name of your script

GroupBy ItemCode;

Re: Script Question

bnelson111 — Fri, 08 Mar 2013 09:02:32 GMT

Thanks for the rar file but im still learning using the personal license.

Thanks for the replies, I must be doing something wrong, See below:

Resident TableName Start: Is underlined in Red and also Groupby ItemCode;

Load script is named Start. Maybe its where im placing this Load instruction? Should it be after Start script?

Difference:

Load

ItemCode,

Max([Day New Plate Received]) - Max([Day New Plate Ordered]) AS NumberOfDays

Resident TableName Start:
//table name of your script

GroupBy ItemCode;

Re: Script Question

Fri, 08 Mar 2013 10:47:47 GMT

Hi,

You can use this script in back end

Interval(Received-Plateordered,'DD') AS Day_Difference

If its is not working in back end then use below mentioned script in charts or listbox

Interval(Received-Plateordered,'DD')

Thanks & Regards

Sampath

Re: Script Question

bnelson111 — Fri, 08 Mar 2013 10:58:14 GMT

Brilliant used the list box.

Re: Script Question

bnelson111 — Fri, 08 Mar 2013 11:34:26 GMT

How can i get the list box not to show items that dont have days difference, ie dont display items with no date difference on list box

Re: Script Question

Fri, 08 Mar 2013 12:05:22 GMT

Hi,

Please find the below mentioned if condition.

This Expression you can use in the List Box.

Select Expression in listbox and paste this condition. You will get the result.

=if(Received>0,ItemCode)