Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
];
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
];
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
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!
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;
Hi Sunny, thanks for your answer, but doesn't work, I have result "-" in firstInvoice field
Best regards,
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;
I send as screenshot, real field names will used, thanks!
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
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