Wrong Count with Group By in Load Script

[yashpace]

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!

[lblumenfeld]

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?

[yashpace]

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!