topic Re: Add field (firstInvoice) in QlikView

Add field (firstInvoice)

Gabri — Fri, 07 Feb 2020 12:57:52 GMT

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!

Re: Add field (firstInvoice)

Stoyan_Terziev — Fri, 07 Feb 2020 13:22:17 GMT

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 ];

Re: Add field (firstInvoice)

Gabri — Fri, 07 Feb 2020 14:19:30 GMT

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

Re: Add field (firstInvoice)

Gabri — Tue, 11 Feb 2020 11:34:18 GMT

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

Stoyan code:

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!

Re: Add field (firstInvoice)

sunny_talwar — Tue, 11 Feb 2020 12:17:58 GMT

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;

Re: Add field (firstInvoice)

Gabri — Tue, 11 Feb 2020 13:02:21 GMT

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

Best regards,

Re: Add field (firstInvoice)

sunny_talwar — Tue, 11 Feb 2020 13:04:28 GMT

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;

Re: Add field (firstInvoice)

Gabri — Tue, 11 Feb 2020 14:08:16 GMT

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

Re: Add field (firstInvoice)

sunny_talwar — Tue, 11 Feb 2020 14:10:40 GMT

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

Re: Add field (firstInvoice)

Gabri — Tue, 11 Feb 2020 14:30:40 GMT

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

Re: Add field (firstInvoice)

sunny_talwar — Tue, 11 Feb 2020 16:19:06 GMT

Does this table also have the same CODCLI "bd1".dbo.CLIENTS as do "bdi".dbo.CABEFACV? The join will only work if the field values align. For example, if one table has 2, 4, 6 and the other one have 1, 3, 5... the join will not work

Re: Add field (firstInvoice)

Gabri — Tue, 11 Feb 2020 17:09:25 GMT

"bd1".dbo.CLIENTS have all clients, so have (1,2,3,4,5.....)

"bdi".dbo.CABEFACV have all invoices, so its possible that have (2,3,3,3,4,8...)

Re: Add field (firstInvoice)

sunny_talwar — Tue, 11 Feb 2020 17:16:09 GMT

It should have worked, but I am not sure why it hasn't.

Re: Add field (firstInvoice)

Gabri — Tue, 11 Feb 2020 21:38:11 GMT

Hello, I've been testing and finally with a new table, only with (cliendid, firstInvoice), and it works !

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

Thanks for your time!

Best regards,

Gabri