Conditional fill empty cells with value from below

[Munkaran]

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

[Taoufiq_Zarra]

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 :

[Munkaran]

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.

[Taoufiq_Zarra · Accepted solution]

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;

[Munkaran]

Ok, thank you.

Thank has worked perfectly.