Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am getting the wrong count while using group by with multiple fields in the following script :-
OrderDetails:
LOAD
count(distinct ORDNUM) as OrderCount,
Date(floor(ADDDTE)) as ADDDTE,
Date(floor(PRCDTE)) as PRCDTE,
Date(floor(DISPATCH_DTE)) as DISPATCH_DTE
FROM [lib://Desktop/RP_Sample.xlsx](ooxml, embedded labels, table is Sheet1)
group By
Date(floor(ADDDTE)),
Date(floor(PRCDTE)),
Date(floor(DISPATCH_DTE));
Exit Script;
attaching the data I am using and the screenshots.
Will appreciate any suggestions.
Thanks!
I think the issue is that you're grouping by the field combinations and then viewing the data by individual field. Are you just looking to get a count for each individual date field? If so, then you don't need to create a table with this. Just load your data.
For example,
Load
ORDNUM,
Date(floor(ADDDTE)) as ADDDTE,
Date(floor(PRCDTE)) as PRCDTE,
Date(floor(DISPATCH_DTE)) as DISPATCH_DTE
From yourdatasource...
Then in your tables:
Dimension: ADDDTE
Measure: Count(distinct ORDNUM)
would give you a count of (distinct) orders for that date
or...
Dimension: ORDNUM
Measure: Count(distinct ADDDTE)
would give you a count of (distinct) ADDDTEs for that each order
You can also use multiple dimensions, etc....
Is this what you're looking for?
Actually I am doing exactly the same thing currently, what you explained. But I have just uploaded a sample file, I have huge data with many more fields. Which is giving me performance issues while I Count for a given day because it has to look in all of the data. I am just trying to do this in the backend load script to improve app's performance.
I think you are right I will need to try a different approach.
Thank you so much!