Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What is the database ?
What is the Language?
are you using only SQL?
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
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
Avoid stored procedures for this kind of process.
Stored procedures are Database dependent.
Antonio Caria
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)