Script Question

[bnelson111]

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 ------

[CELAMBARASAN]

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

[#3]

Hi ,

    Please find the attached file for your solution.

[bnelson111]

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;

[bnelson111]

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;

[#6 · Accepted solution]

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

[bnelson111]

Brilliant used the list box.

[bnelson111]

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

[#9]

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)