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
