Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bnelson111
Creator
Creator

Loading SQL Records to review oldest date recorded.

Everyone,

Below is a script to load data which contains multiple records for each Plate Code, for example the plate may have been used last week and made 1000 items against a job number then again today for a different job for 500, i would like to select either via script or expression the must recent date that the plate was last used for review. below is script and example of basic table showing a plate used on two dates. (So i want to for review only use 06/05/2013?

Thanks in advance. Brett

qlikview 1.png

ODBC

CONNECT TO ****** (XUserId is JRTDMRZNFDbGXXZNJF, XPassword is FCPUDRZNFDbGXXZNMF);

SQL SELECT ToolStatus,
    ToolStatusDesc
FROM PUB."PV_ToolStatus";

SQL SELECT
   completed,
    event,
    history,
    toolRunQty as JobRunQty,
    toolCode as PlateCode,
    toolType
  FROM PUB."PV_JobToolHistory";

SQL SELECT DeliveryDue,
    LastMoveDate,
    OrderDate,
    RunTotal,
    ToolCode as PlateCode,
    ToolStatus
FROM PUB."PV_Tools";

// Loading Temp File
TEMP:
SQL SELECT completed
FROM PUB."PV_JobToolHistory";
DATA:
LOAD (completed),
       
Date(completed) as [Date Job Ran],
       
Day(completed) as [Day Job Ran],
       
Month(completed) as [Month Job Ran],
       
Year(completed) as [Year Job Ran]

Resident TEMP;
Drop table TEMP;

2 Replies
anandathome
Creator
Creator

Hi,

    If I have understood you correctly, modification of this should work for you.

Temp:

LOAD * INLINE [

    Date, Completed, PlateCode

    1/4/2013, 10, 649

    7/5/2013, 8, 649

    1/12/2012, 5, 700

    1/3/2013, 4, 700

    8/5/2013, 20, 101

];

Final:

Load Date(max(Date),'DD-MMM-YYYY'),

     sum(Completed),

     PlateCode

Resident Temp

group by PlateCode;

   drop Table Temp;

-Anand

bnelson111
Creator
Creator
Author

Thanks but unfortunately not working, i just need to se the last or latest record info for the Plate code. if the plate has run 3 times in the last 4 weeks or whatever the time is i need to see the record for the last run date if for example that was today, hope this makes sense?