topic Loading SQL Records to review oldest date recorded. in QlikView

Loading SQL Records to review oldest date recorded.

bnelson111 — Wed, 08 May 2013 10:58:06 GMT

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

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;

Re: Loading SQL Records to review oldest date recorded.

anandathome — Wed, 08 May 2013 11:27:06 GMT

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

Re: Loading SQL Records to review oldest date recorded.

bnelson111 — Wed, 08 May 2013 15:36:35 GMT

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?