Add field (firstInvoice)

[Gabri]

Hi, I want to add a new field on load data from SQL with name (firstInvoice), for example, I have to tables:

CLIENTS
CLIENTID	NAME
1	AA
2	BB
3	CC
INVOICES
CLIENTID	DATE	AMOUNT
1	07/02/2018	10
2	09/02/2018	15
1	10/05/2019	10
2	15/06/2019	20
2	05/09/2019	20
3	05/01/2020	10
1	15/01/2020	10
1	20/01/2020	20
2	01/02/2020	10
3	04/02/2020	10
3	06/02/2020	20

For each client I need to say  [ SELECT min(year(DATE) from INVOICES) as firstInvoice ]

The desired result is:

CLIENTS
CLIENTID	NAME	firstInvoice
1	AA	2018
2	BB	2018
3	CC	2020

Thanks in advance!

[Stoyan_Terziev · Accepted solution]

Hi Gabri,

Here's the code that will do that.

I hope that helps!

Kind regards,

S.T.

	
CLIENTS:
LOAD * INLINE [
    CLIENTID, NAME
    1, AA
    2, BB
    3, CC
];


//INVOICES Entry Dataset
LEFT JOIN(CLIENTS)
	LOAD
		CLIENTID,
	 	MIN(YEAR(Date(Date#(DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY'))) 	as firstInvoice
	 GROUP BY
	 CLIENTID
		//Here we make sure the dateformat is clearly identified
;
	LOAD * INLINE [
	    CLIENTID, DATE, AMOUNT
	    1, 07/02/2018, 10
	    2, 09/02/2018, 15
	    1, 10/05/2019, 10
	    2, 15/06/2019, 20
	    2, 05/09/2019, 20
	    3, 05/01/2020, 10
	    1, 15/01/2020, 10
	    1, 20/01/2020, 20
	    2, 01/02/2020, 10
	    3, 04/02/2020, 10
	    3, 06/02/2020, 20
	];

[Gabri]

Hi Stoyan, thanks for your quick answer, it works fine in a new app, but can you help me to add your code in my current script?

----------------------------

LIB CONNECT TO 'Microsoft_SQL_Server';

LOAD CLIENTID,
NAME;

[CLIENTS]:
SELECT CLIENTID,
NAME
FROM "bd1".dbo.CLIENTES;

LOAD CLIENTID,
DATE,
AMOUNT;

[INVOICES]:
SELECT CLIENTID,
DATE,
AMOUNT
FROM "bd1".dbo.INVOICES;

----------------------------

King regards,

Gabri

[Gabri]

Hi all, can someone help me to use Stoyan code in my current script?

Stoyan code:

CLIENTS:
LOAD * INLINE [
    CLIENTID, NAME
    1, AA
    2, BB
    3, CC
];


//INVOICES Entry Dataset
LEFT JOIN(CLIENTS)
	LOAD
		CLIENTID,
	 	MIN(YEAR(Date(Date#(DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY'))) 	as firstInvoice
	 GROUP BY
	 CLIENTID
		//Here we make sure the dateformat is clearly identified
;
	LOAD * INLINE [
	    CLIENTID, DATE, AMOUNT
	    1, 07/02/2018, 10
	    2, 09/02/2018, 15
	    1, 10/05/2019, 10
	    2, 15/06/2019, 20
	    2, 05/09/2019, 20
	    3, 05/01/2020, 10
	    1, 15/01/2020, 10
	    1, 20/01/2020, 20
	    2, 01/02/2020, 10
	    3, 04/02/2020, 10
	    3, 06/02/2020, 20
	];

My script:

LIB CONNECT TO 'Microsoft_SQL_Server';

LOAD CLIENTID,
NAME;

[CLIENTS]:
SELECT CLIENTID,
NAME
FROM "bd1".dbo.CLIENTS;


LOAD CLIENTID,
DATE,
AMOUNT;

[INVOICES]:
SELECT CLIENTID,
DATE,
AMOUNT
FROM "bd1".dbo.INVOICES;

Thanks in advance!

[sunny_talwar]

May be this

LIB CONNECT TO 'Microsoft_SQL_Server';

LOAD CLIENTID,
NAME;

[CLIENTS]:
SELECT CLIENTID,
NAME
FROM "bd1".dbo.CLIENTS;

[INVOICES]:
LEFT JOIN(CLIENTS)
LOAD CLIENTID,
     Min(Year(Date#(DATE, 'DD/MM/YYYY'))) as firstInvoice
GROUP BY CLIENTID;
SELECT CLIENTID,
DATE,
AMOUNT
FROM "bd1".dbo.INVOICES;

and if your date is read as a date field with underlying numeric value, then try this

LIB CONNECT TO 'Microsoft_SQL_Server';

LOAD CLIENTID,
NAME;

[CLIENTS]:
SELECT CLIENTID,
NAME
FROM "bd1".dbo.CLIENTS;

[INVOICES]:
LEFT JOIN(CLIENTS)
LOAD CLIENTID,
     Min(Year(DATE)) as firstInvoice
GROUP BY CLIENTID;
SELECT CLIENTID,
DATE,
AMOUNT
FROM "bd1".dbo.INVOICES;

[Gabri]

Hi Sunny, thanks for your answer, but doesn't work, I have result "-" in firstInvoice field

Best regards,

[sunny_talwar]

Would you be able to share a screenshot of DATE field in a list box object after running just this query

SELECT CLIENTID,
DATE,
AMOUNT
FROM "bd1".dbo.INVOICES;

[Gabri]

I send as screenshot, real field names will used, thanks!

[sunny_talwar]

Try this

LIB CONNECT TO 'Microsoft_SQL_Server';

LOAD CLIENTID,
NAME;

[CLIENTS]:
SELECT CLIENTID,
NAME
FROM "bd1".dbo.CLIENTS;

[INVOICES]:
LEFT JOIN(CLIENTS)
LOAD CLIENTID,
     Min(Year(Date#(SubField(DATE, ' ', 1), 'YYYY-MM-DD'))) as firstInvoice
GROUP BY CLIENTID;
SELECT CLIENTID,
DATE,
AMOUNT
FROM "bd1".dbo.INVOICES;

and see if this works for you

[Gabri]

Hi Sunny, I have the same result, also I checked with other field, to do a test, this field is (NUMDOC) and is numeric, I have the same result "-".

See attachment

Thanks