Script + Distinct

[Anonymous]

Hi All,

Would appreciate some help with the following script problem

Data:

Load Date(Defect_Date,'MMYY') as DefectDate

From Filename.xlsx;

Now I want only distinct values

Data2:

Load DISTINCT DefectDate

Resident Data;

But the above  script is not getting the distinct values

[PrashantSangle]

Hi,

did your date field contain time stamp also.

In that case date(dateField,'MMYY') won't work

try with floor

date(floor(dateField),'MMYY')

Regards

[Anonymous]

Data:

Load DISTINCT  Date(MakeDate(Year(Defect_Date),Month(Defect_Date),1) 'MMYY') as DefectDate

From Filename.xlsx;

[stevedark]

You are formatting to make the field show only the month, but the underlying value is a date (or even a date time).

You will see this if you create a column in a straight table with Date(DefectDate, 'DD MMM YYYY') as the expression.

In order only to get months you need to remove the day part during the load script:

Data:

Load Date(MonthStart(Defect_Date),'MMYY') as DefectDate

From Filename.xlsx;

The MonthStart function will return midnight at the start of the 1st of each month in your data set.

I don't know what your end requirement is, but I doubt you need the second part of doing the LOAD DISTINCT, as QlikView only shows distinct values in List Boxes and as Chart Dimensions anyway.

Hope that helps,

Steve