topic Script + Distinct in QlikView

Script + Distinct

Anonymous — Tue, 17 May 2016 07:26:20 GMT

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

Re: Script + Distinct

tresB — Tue, 17 May 2016 07:29:45 GMT

Try like:

Data:

Load Date(Defect_Date,'MMYY') as DefectDate

From Filename.xlsx;

Noconcatenate

Data2:

Load DISTINCT DefectDate

Resident Data;

Drop table Data;

Re: Script + Distinct

tamilarasu — Tue, 17 May 2016 07:29:53 GMT

Try like below,

Data:

LOAD Date(Defect_Date,'MMYY') as Defect_Date

From Filename.xlsx;

NoConcatenate

Data2:

Load DISTINCT Defect_Date

Resident Data;

DROP Table Data;

Re: Script + Distinct

Anonymous — Tue, 17 May 2016 07:37:33 GMT

Tried.... it is not getting distinct rows.

Re: Script + Distinct

Anonymous — Tue, 17 May 2016 07:37:47 GMT

Tried.... it is not getting distinct rows.

Re: Script + Distinct

tamilarasu — Tue, 17 May 2016 07:42:31 GMT

Simply try,

Data:

LOAD Distinct Date(Defect_Date,'MMYY') as Defect_Date

From Filename.xlsx;

If not working, please post a sample file.

Re: Script + Distinct

tresB — Tue, 17 May 2016 07:43:54 GMT

It could probably be a formatting issue. The Distinct would work on values rather than formatting. Why don't you get the distinct values directly from the source like: ?

Data:

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

From Filename.xlsx;

Re: Script + Distinct

Kushal_Chawda — Tue, 17 May 2016 07:44:48 GMT

Data:

Load Date(Defect_Date,'MMYY') as DefectDate

From Filename.xlsx;

Data2:

Load DISTINCT DefectDate1

Resident Data;

Now check the Frequency of DefectDate1 in listbox. it should be 1 for all dates

Re: Script + Distinct

sunny_talwar — Tue, 17 May 2016 08:44:40 GMT

I think just because you are formatting Defect_Date as MMYY doesn't mean that you have got rid of the Day part of the expression. When doing a distinct, you would still see all the Defect_Date for each of the day. To get rid of the day part, you can try like this:

Data:

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

From Filename.xlsx;

Now I want only distinct values

Data2:

NoConcatenate

Load DISTINCT DefectDate

Resident Data;

MonthStart will make sure that all the dates in a particular month are floored to the 1st day of the month and now when you find the Distinct DefectDate, you will get only distinct list of MonthStarts.

Also added NoConcatenate since both table have only one field and both of the two fields are the same. You might not need this in your actual scenario, but I don't really know how you plan on using this

Re: Script + Distinct

Gabriel — Tue, 17 May 2016 08:50:35 GMT

Hi,

If you've tried all suggestions above and no solution from them all.

I think the best thing to do now is to attach sample data.

Re: Script + Distinct

PrashantSangle — Tue, 17 May 2016 08:58:36 GMT

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

Re: Script + Distinct

Anonymous — Tue, 17 May 2016 12:38:06 GMT

Data:

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

From Filename.xlsx;

Re: Script + Distinct

stevedark — Tue, 17 May 2016 12:42:54 GMT

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