Script Issue in Max Function

[Anonymous]

Hi!  I'm trying to get the latest date on the Snaphot_Week field by using the max in my where clause but it doesn't work.  Is there any way to get the latest date?

LOAD*

Resident InventorySummary_temp;
Left join

LOAD DATE(Snapshot_Week) as Snaphot_Week,

     Date(Snapshot_Date) as Snapshot_Date,
     BasePartNumber as [Base Material],
     Plant,
     BasePartNumber & '|' & Plant AS LeanPartPlantKey

FROM

[..\..\Stage 1\Stage 1 Local QVDs\Mhub\Stage1_Archive_LeanParts.qvd]

qvd)

   where max(Snapshot_Week);

Tks.

[Peter_Cammaert]

Max() is an aggregation function, to be used only in LOAD statements that have a GROUP BY clause (there are some exceptions, but your load isn't one of them)

You probably get this weird error message because it is an invalid function in a WHERE clause.

[Peter_Cammaert]

You better use something like:

:

LEFt JOIN

LOAD Max(Snapshot_Date) AS Snapshot_Date,

          BasePartNumber AS [Base Material],

          Plant

RESIDENT YourLeanPartsQVDLoadedAsResidentTable

GROUP BY BasePartNumber, Plant;

:

or something similar. I cannot see what your JOIN fields are.

Best,

Peter

[its_anandrjs]

Hi,

Yes peter is right you have to load any resident table to get max column like

LOAD*

Resident InventorySummary_temp;
Left join

LOAD DATE(Snapshot_Week) as Snaphot_Week,

     Date(Snapshot_Date) as Snapshot_Date,
     BasePartNumber as [Base Material],
     Plant,
     BasePartNumber & '|' & Plant AS LeanPartPlantKey
FROM

[..\..\Stage 1\Stage 1 Local QVDs\Mhub\Stage1_Archive_LeanParts.qvd] (qvd);

left Join

Load

LeanPartPlantKey,

Max(Snapshot_Week) as Max_Snapshot_Week

Resident Yourtablename

Group By LeanPartPlantKey;

Then you are able to check

MainTable:

Load *

Resident Yourtablename

where Snapshot_Week = Max_Snapshot_Week;

Drop Table Yourtablename;

Hope this helps

Thanks & Regards

[arsal_90]

LOAD*

Resident InventorySummary_temp;
Left join

LOAD DATE(Snapshot_Week) as Snaphot_Week,
Date(Snapshot_Date) as Snapshot_Date,
BasePartNumber as [Base Material],
Plant,
BasePartNumber & '|' & Plant AS LeanPartPlantKey

FROM

[..\..\Stage 1\Stage 1 Local QVDs\Mhub\Stage1_Archive_LeanParts.qvd]

qvd) where max(Snapshot_Week)

group by BasePartNumber, Plant;

[Anonymous]

Hi! Peter,

Sorry but I don't quite get it...I'm still new to Qlikview. Anyway, I'm attaching my qvw for better unerstanding for what I would like to do...The script error is in the Summary tab.

Tks.

[Anonymous]

Muhammad,

I tried it but still didn't work.

Tks.

[#8]

MaxDate:

Load max(Snapshot_Week) as MaxWeek;

LOAD*

Resident InventorySummary_temp;
Left join

LOAD DATE(Snapshot_Week) as Snaphot_Week,

     Date(Snapshot_Date) as Snapshot_Date,
     BasePartNumber as [Base Material],
     Plant,
     BasePartNumber & '|' & Plant AS LeanPartPlantKey

FROM

[..\..\Stage 1\Stage 1 Local QVDs\Mhub\Stage1_Archive_LeanParts.qvd]

qvd)

;

Let vMaxWeek = peed('MaxWeek',0,'MaxDate');

drop table Maxdate;

Table:

LOAD*

Resident InventorySummary_temp;
Left join

LOAD DATE(Snapshot_Week) as Snaphot_Week,

     Date(Snapshot_Date) as Snapshot_Date,
     BasePartNumber as [Base Material],
     Plant,
     BasePartNumber & '|' & Plant AS LeanPartPlantKey

FROM

[..\..\Stage 1\Stage 1 Local QVDs\Mhub\Stage1_Archive_LeanParts.qvd]

qvd)

where DATE(Snapshot_Week) = $(vMaxWeek)

;