topic Re: SQL Create and Insert Table and join to qvd in QlikView

SQL Create and Insert Table and join to qvd

Fri, 12 Dec 2014 16:10:19 GMT

Hi All,

I have a payments table that is showing the max number of payments a customer has made and if they are still paying or they have cancelled:

Customer ID	# Payments	Payment Status
123	4	Cancelled
456	5	Paying
789	2	Paying
012	1	Cancelled

But I want to see a row for ever time they pay:

e.g:

Customer ID	Payment	Payment Status
123	1	Paid
123	2	Paid
123	3	Paid
123	4	Cancelled
456	1	Paid
456	2	Paid
456	3	Paid
456	4	Paid
456	5	Paid
789	1	Paid
789	2	Paid
012	1	Cancelled

In SQL I have been able to use a Create Table and Insert Into:

CREATE TABLE #num

( i INT NOT NULL

, PRIMARY KEY

) ;

INSERT INTO #num

VALUES

(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18),

(19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36),

(37), (38), (39), (40), (41), (42), (43), (44), (45), (46), (47), (48), (49), (50), (51), (52), (53), (54)

That I then join to my payments table by joining on the max number of payments and looking up the status:

select t.*, #num.i as [Tenure Month],

case when t.[NumberOfPayments] = #num.i then t.[PaymentStatus] else 'Paid' end as [PaymentStatus]

from

Payments t

join #num

on #num.i <= t.[NumberOfPayments]

I cannot get this to work in Qlikview! The problem seems to be with the create and insert statements.

Any help would be much appreciated!

Thanks

Re: SQL Create and Insert Table and join to qvd

Anonymous — Fri, 12 Dec 2014 16:38:51 GMT

What is the database ?

What is the Language?

are you using only SQL?

Re: SQL Create and Insert Table and join to qvd

Fri, 12 Dec 2014 16:44:45 GMT

It is an SQL database and I have got the script to work in SQL but when i try and do the same in qlikview the "sql create table..." and "sql insert into..." statements error

Re: SQL Create and Insert Table and join to qvd

agomes1971 — Fri, 12 Dec 2014 16:51:52 GMT

Hi,

First build store procedures in your database that do what you want.

Then execute store procedures doing sql exec yourstoreprocedure

and then

go to edit script

settings

check open databases in read and write mode~

check can execute external programs

I think that's enough!!!

Regards

André Gomes

Re: SQL Create and Insert Table and join to qvd

Anonymous — Fri, 12 Dec 2014 16:54:35 GMT

Avoid stored procedures for this kind of process.

Stored procedures are Database dependent.

Antonio Caria

Re: SQL Create and Insert Table and join to qvd

Anonymous — Fri, 13 Mar 2015 07:10:37 GMT

Hi,
I observe that your procedure is inserting records in database, so you should use ODBC connection in write mode and enable "Open Database in read and write mode" from settings.
Ex.: ODBC CONNECT TO [DB](Mode is write); Use ODBC connection in QliK View script and open the conneciton in Write mode
ex.:ODBC CONNECT TO [DB](Mode is write)