Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try like:
Data:
Load Date(Defect_Date,'MMYY') as DefectDate
From Filename.xlsx;
Noconcatenate
Data2:
Load DISTINCT DefectDate
Resident Data;
Drop table Data;
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;
Tried.... it is not getting distinct rows.
Tried.... it is not getting distinct rows.
Simply try,
Data:
LOAD Distinct Date(Defect_Date,'MMYY') as Defect_Date
From Filename.xlsx;
If not working, please post a sample file.
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;
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
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
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.