Group by on a specific field value

[Aditya_Chitale]

Hi All,

I have loaded table as below

Order No	Batch No	Customer Name	Date
1	99308	ABC	01-04-2022
2	99292	ABC	05-31-2022
3	99310	XYZ	06-19-2022
4	99018	XYZ	06-09-2022
4	99313	PQR	04-02-2022
3	99310	UVW	01-01-2022

As seen there are multiple dates for some customer names. I want only the minimum date for Customer ABC (Here 01-04-2022). Tried using Group by, but didn't work since I have calculated measures in my table.

Any help would be appreciated

Regards,

Aditya

[Aditya_Chitale]

Hi All,

I have loaded table as below

Order No	Batch No	Customer Name	Date
1	99308	ABC	01-04-2022
2	99292	ABC	05-31-2022
3	99310	XYZ	06-19-2022
4	99018	XYZ	06-09-2022
4	99313	PQR	04-02-2022
3	99310	UVW	01-01-2022

As seen there are multiple dates for some customer names. I want only the minimum date for Customer ABC (Here 01-04-2022). Tried using Group by, but didn't work since I have calculated measures in my table.

Any help would be appreciated

Regards,

Aditya

[hic · Accepted solution]

There are several ways to do this, but one simple way is to use an inner join:

Data:
Load ... From <Source> (...) ;

Inner Join (Data)
Load [Customer Name], Date(Max(Date),'MM-DD-YYYY') as Date
Resident Data
Group By [Customer Name];

[Aditya_Chitale]

@hic 

It worked using inner join upto an extent. But Now I am facing another issue. I have a field created using RowNo(Total) in my data. Which I apologise for not mentioning in the previous sample.

Now, I want to show corresponding row number value against my aggregated min(Date). 

eg.

Order No	Batch No	Customer Name	Date	RowNo(TOTAL)
1	99308	ABC	05-31-2022	1
2	99292	ABC	01-04-2022	2
3	99310	XYZ	06-19-2022	3
4	99018	XYZ	06-09-2022	4
4	99313	PQR	04-02-2022	5
3	99310	UVW	01-01-2022	6

I want to only include RowNo value as 2 for min(Date): 01-04-2022

I tried including following line in my code (Group by table part):

Load

min(RowNo) as Min_Row_No

Resident Data
Group By [Customer Name];

But, I am not able to get row number for particular  min(Date) as I am grouping on only customer name.

I also  tried using firstSortedValue(RowNo,Date) but got null values in Min_Row_No  field. Hope I am able to explain my issue clearly. Is there any way to achieve the specified result?

Regards,

Aditya

[Aditya_Chitale]

Resident Data
Group By [Customer Name];

But, I am not able to get row number for particular  min(Date) as I am grouping on only customer name.

I also  tried using firstSortedValue(RowNo,Date) but got null values in Min_Row_No  field. Hope I am able to explain my issue clearly. Is there any way to achieve the specified result?

Regards,

Aditya

[vchuprina]

Hi Aditya,

Could you please show an example with the expected result for your table

Regards,

Vitalii

[Aditya_Chitale]

Hello @vchuprina ,

Sample Data:

Order No	Batch No	Customer Name	Date	RowNo(TOTAL)
1	99308	ABC	05-31-2022	1
2	99292	ABC	01-04-2022	2
3	99310	XYZ	06-19-2022	3
4	99018	XYZ	06-09-2022	4
4	99313	PQR	04-02-2022	5
3	99310	UVW	01-01-2022	6

Desired Output:

Order No	Batch No	Customer Name	Date	RowNo(TOTAL)
2	99292	ABC	01-04-2022	2
3	99310	XYZ	06-19-2022	3
4	99018	XYZ	06-09-2022	4
4	99313	PQR	04-02-2022	5
3	99310	UVW	01-01-2022	6

So basically, I want to show customer ABC with least value of Date and all the other records corresponding to that row (Order No, Batch No & Row No).

I have achieved the result where I am able to get Customer wise min Date. Now I Need logic to pick Row No value corresponding to that date record

Regards,

Aditya

[vchuprina]

Aditya,

Just add RowNo() as RowNo in your  script

Try this

DATA:
LOAD
   [Order No],
   [Batch No],
   [Customer Name],
   Date(Date#(Date, 'MM-DD-YYYY')) AS Date,
   RowNo() AS RowNo;
LOAD * Inline[
 Order No, Batch No, Customer Name, Date 
 1, 99308, ABC, 05-31-2022
 2, 99292, ABC, 01-04-2022
 3, 99310, XYZ, 06-19-2022
 4, 99018, XYZ, 06-09-2022
 4, 99313, PQR, 04-02-2022
 3, 99310, UVW, 01-01-2022
];

Inner Join (DATA)
Load [Customer Name],
Date(Min(Date)) as Date
Resident DATA
Group By [Customer Name];

Regards,

Vitalii