Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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];
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
Hi Aditya,
Could you please show an example with the expected result for your table
Regards,
Vitalii
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
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