Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

SQL Create and Insert Table and join to qvd

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
1234Cancelled
4565Paying
7892Paying
0121Cancelled

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

e.g:

Customer ID
Payment
Payment Status
1231Paid
1232Paid
1233Paid
1234Cancelled
4561Paid
4562Paid
4563Paid
4564Paid
4565Paid
7891Paid
7892Paid
0121Cancelled

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

5 Replies
bestofwest
Contributor II

Re: SQL Create and Insert Table and join to qvd

What is the database ?


What is the Language?


are you using only SQL?



Not applicable

Re: SQL Create and Insert Table and join to qvd

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

agomes1971
Valued Contributor

Re: SQL Create and Insert Table and join to qvd

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

bestofwest
Contributor II

Re: SQL Create and Insert Table and join to qvd

Avoid stored procedures for this kind of process.

Stored procedures are Database dependent.

Antonio Caria

meet_purushu
New Contributor III

Re: SQL Create and Insert Table and join to qvd

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.Smiley SurprisedDBC CONNECT TO [DB](Mode is write)

Community Browser