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

[tresesco]

Try like:

Data:

Load Date(Defect_Date,'MMYY') as DefectDate

From Filename.xlsx;

Noconcatenate

Data2:

Load DISTINCT DefectDate

Resident Data;

Drop table Data;

[tamilarasu]

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;

[Anonymous]

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

[Anonymous]

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

[tamilarasu]

Simply try,

Data:

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

From Filename.xlsx;

If not working, please post a sample file.

[tresesco]

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;

[Kushal_Chawda]

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

[sunny_talwar · Accepted solution]

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

[Gabriel]

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.