Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
balamosur
Contributor
Contributor

Need to highlight rows matching in table A v/s Table B based on a status in Table B

Hi,

 

I am very much new to Qlik View and have a requirement of Highlighting rows matching in table A v/s Table B based on 1. The key field called OrderNumber and 2. status column in Table B. First objective if to show rows in Red in Table A which do not match and then if they match then based on Status = "Available" it will be green or if Status = "Not Available" then it will be  yellow. Both the table have slightly different set of columns but have a common column which is a calculated column. Basically,created a additional column in both table by using the mid function as I had to strip of the initial character to match-up. 

Given below are my 2 table script

Table A:
LOAD
A as [Order Number],
mid([A],4,len([A])) AS OrderNumber
FROM
[Table A.xlsx]
(ooxml, no labels);

 

Table B:
LOAD [Shipment ID],
mid([Shipment ID],4,len([Shipment ID])) AS OrderNumber,
[Status],
FROM
[Table B.xlsx]
(ooxml, embedded labels, table is [TM Export Data]);

Also because I am using the Qlikview PE version to try out thing I am not able to import any .qvw files. So would appreciate if anyone can lend any help here.

 

Thanks and Regards,

Bala Mosur

3 Replies
arpitkharkia
Creator III
Creator III

You can perform a left join between Table A and Table B. For all the fields where status is blank is red, and the rest two colors can be according to your logic.

balamosur
Contributor
Contributor
Author

Hi Arpit,
Can you please let me know how I can do that? Do I need to modify the load table script for that or create another table with the left join? And then how do I highlight the row. Unfortunately, I am using a PE version so will not be able to import any qvw files so will appreciate your response here.
TR,
Bala
arpitkharkia
Creator III
Creator III

Table A:
LOAD
A as [Order Number],
mid([A],4,len([A])) AS OrderNumber
FROM
[Table A.xlsx]
(ooxml, no labels);

left join

Table B:
LOAD [Shipment ID],
mid([Shipment ID],4,len([Shipment ID])) AS OrderNumber,
[Status],
FROM
[Table B.xlsx]
(ooxml, embedded labels, table is [TM Export Data]);

noconcatenate

TableC:

Load *,if(isnull(Status),'Red',Status) as New_Status

resident TableA;

Use the New_Status field in expressions for color