Group by Ref Number and use the Min date

[RichardLee]

I have a table of data and I need to group it by a ref number but I also Want to use the First date it was used I've tried a few combinations and all failed.

Invoices:

LOAD
'France'as Site,
EC_Reference,
EC_No,
CG_Num,
CT_Num as "Customer Acc Code2",
EC_CGNUM,
EC_CTNUM,
EC_ECECHEANCE,
EC_ECLETTRE,
EC_ECMONTANT_C,
EC_ECMONTANT_D,
EC_JMDATE,
EC_ECECHEANCE as "DueDate",
'EUR' &Date(EC_JMDATE,'MMM')&Year(EC_JMDATE) as "CurrKey",
EC_ECMONTANT_D-EC_ECMONTANT_C as "Gross Amount",
if((EC_ECECHEANCE-today())>=0,'Current',if((EC_ECECHEANCE-today())>='-5','1-5',if((EC_ECECHEANCE-today())>='-30','6-30',if((EC_ECECHEANCE-today())>='-60','31-60',If((EC_ECECHEANCE-today())>='-90','61-90','+90'))))) as "Category",
if((EC_ECECHEANCE-today())>=0,'Current','Overdue') as "OD Flag",
if( Date(Floor(EC_ECECHEANCE))<today(),today()-Date(Floor(EC_ECECHEANCE)),'') as "Days Due from Today",
applymap('MapSalesPerson',Num(CT_Num),'Unknown') as [Sales Person]
;

[vF_ECRITUREC]:
SELECT "CG_Num",
"EC_Reference",
"EC_No",
"CT_Num",
"EC_CGNUM",
"EC_CTNUM",
"EC_ECECHEANCE",
"EC_ECLETTRE",
"EC_ECMONTANT_C",
"EC_ECMONTANT_D",
"EC_JMDATE"


FROM "SAGE_SAGECOMPTA".dbo."vF_ECRITUREC" WHERE EC_CGNUM ='41100000' and EC_ECLETTRE ='NON' and EC_JMDate >='01/01/2022'
;

[Or]

Sounds like you need something along the lines of:

Load ReferenceNumber, Date(Min(WhenUsed)) as FirstUsed

From YourTable

Group by ReferenceNumber;

[RichardLee]

Do I put it in a new Section at the end of the Original Data load?

I use PowerBI for most things and Qlik is still new to me 

[Or]

I'm not sure what you're trying to achieve, so it's hard for me to try and answer that question. That specific query should get you the first used date grouped by reference number, which is really the same thing as the SQL query:

Select ReferenceNumber, Min(WhenUsed) as FirstUsed

From SomeTable

Group by ReferenceNumber;

[RichardLee]

its ok I went a different way.

FrenchInvoiceGroup:
Load

EC_Reference,
Sum(EC_ECMONTANT_C) as Credit,
Sum(EC_ECMONTANT_D) as Debit

Resident vF_ECRITUREC

Group By EC_Reference;