Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Munkaran
Contributor
Contributor

Conditional fill empty cells with value from below

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_IDORDER_NOCOUPON_USED
 10011
 10011
ACBDE10011
 10020
 10020
 10031
 10031
 10031
FGHIJ10031

 

This is what I would like it to look like:

COUPON_IDCOUPON_ID_ALLORDER_NOCOUPON_USED
 ACBDE10011
 ACBDE10011
ACBDEACBDE10011
  10020
  10020
 FGHIJ10031
 FGHIJ10031
 FGHIJ10031
FGHIJFGHIJ10031
1 Solution

Accepted Solutions
Taoufiq_Zarra

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;
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

4 Replies
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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Munkaran
Contributor
Contributor
Author

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

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;
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Munkaran
Contributor
Contributor
Author

Ok, thank you.

Thank has worked perfectly.