Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am new to QlikSense and need some help with writing a script.
I have a table and I need to create a new column so the COUPON_ID shows on every row of the order. If a voucher has been used in the order then it only appears on the last row of the order, but there is a COUPON_USED column which shows if a coupon has been used against every row of the order.
The order data is fed in automatically every day, and we get a large number of orders so I need an automated way to do this.
This is an example of the current table:
COUPON_ID | ORDER_NO | COUPON_USED |
1001 | 1 | |
1001 | 1 | |
ACBDE | 1001 | 1 |
1002 | 0 | |
1002 | 0 | |
1003 | 1 | |
1003 | 1 | |
1003 | 1 | |
FGHIJ | 1003 | 1 |
This is what I would like it to look like:
COUPON_ID | COUPON_ID_ALL | ORDER_NO | COUPON_USED |
ACBDE | 1001 | 1 | |
ACBDE | 1001 | 1 | |
ACBDE | ACBDE | 1001 | 1 |
1002 | 0 | ||
1002 | 0 | ||
FGHIJ | 1003 | 1 | |
FGHIJ | 1003 | 1 | |
FGHIJ | 1003 | 1 | |
FGHIJ | FGHIJ | 1003 | 1 |
no, inline just an example
for example you can do :
Data:
LOAD
COUPON_ID, ORDER_NO, COUPON_USED;
SQL SELECT *
FROM .......;
left join
load ORDER_NO,MaxString(COUPON_ID) as COUPON_ID_ALL resident Data group by ORDER_NO;
Hi,
Maye be this :
Data:
LOAD * INLINE [
COUPON_ID, ORDER_NO, COUPON_USED
, 1001, 1
, 1001, 1
ACBDE, 1001, 1
, 1002, 0
, 1002, 0
, 1003, 1
, 1003, 1
, 1003, 1
FGHIJ, 1003, 1
];
left join
load ORDER_NO,MaxString(COUPON_ID) as COUPON_ID_ALL resident Data group by ORDER_NO;
output :
Do I need to list all the COUPON_ID's and ORDER_NO's in the first part?
The table in my OP is an example I created, the real data has many thousands of entries so I cannot write them all in the script.
no, inline just an example
for example you can do :
Data:
LOAD
COUPON_ID, ORDER_NO, COUPON_USED;
SQL SELECT *
FROM .......;
left join
load ORDER_NO,MaxString(COUPON_ID) as COUPON_ID_ALL resident Data group by ORDER_NO;
Ok, thank you.
Thank has worked perfectly.