topic Script Issue in Max Function in QlikView

Script Issue in Max Function

Anonymous — Tue, 25 Mar 2014 16:25:00 GMT

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.

Re: Script Issue in Max Function

Peter_Cammaert — Tue, 25 Mar 2014 16:58:05 GMT

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.

Re: Script Issue in Max Function

Peter_Cammaert — Tue, 25 Mar 2014 17:04:57 GMT

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

Re: Script Issue in Max Function

its_anandrjs — Tue, 25 Mar 2014 17:13:28 GMT

Hi,

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

LOAD*

Resident InventorySummary_temp;
Left join

[..\..\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

Re: Script Issue in Max Function

arsal_90 — Tue, 25 Mar 2014 17:16:07 GMT

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;

Re: Re: Script Issue in Max Function

Anonymous — Tue, 25 Mar 2014 17:34:57 GMT

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.

Re: Re: Script Issue in Max Function

Anonymous — Tue, 25 Mar 2014 17:35:44 GMT

Muhammad,

I tried it but still didn't work.

Tks.

Re: Script Issue in Max Function

Tue, 25 Mar 2014 17:51:51 GMT

MaxDate:

Load max(Snapshot_Week) as MaxWeek;

LOAD*

Resident InventorySummary_temp;
Left join

[..\..\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

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

qvd)

where DATE(Snapshot_Week) = $(vMaxWeek)

;