topic Re: Changing Text Color Of One Field Based on Another Field Value in QlikView

Changing Text Color Of One Field Based on Another Field Value

pnn44794 — Sun, 13 Oct 2019 18:52:29 GMT

Hello:

I need to be able to change the text color of the Project Name field to red if there is a match between APP_IDs and L_Application_ID. The APP_IDs field has multiple application ID's. L_Application_ID is being loaded inline with a total of 30 ID's. There's no 1 to 1 alignment to any of the other data and I do not have an actual need to display them anyway. What I believe needs to happen, but I don't know the syntax for, is this:

If APP_ID's like '%L_Application_ID%' Then [Project Name] is Red

There are multiple APP_IDs in the APP_IDs field, separated by a comma and space, i.e. 12345, 67891, etc.

Any and all help will be greatly appreciated. Thanks in advance.

Re: Changing Text Color Of One Field Based on Another Field Value

petter — Fri, 24 Feb 2017 18:14:32 GMT

Re: Changing Text Color Of One Field Based on Another Field Value

pnn44794 — Fri, 24 Feb 2017 18:44:56 GMT

Hello Petter:

Thank you very much for the response. I made the Text changes as above in your screenshots and I made the changes to my load script to match yours. One problem though and this is my fault. I made up the Project Names. They don't all start with "Test Project". Actually none of them do. They are all different names. I suspect that's why any matches are not changing the text to red and not bolding the text. How can we handle different names?

Re: Changing Text Color Of One Field Based on Another Field Value

pnn44794 — Fri, 24 Feb 2017 19:17:19 GMT

One other thing. In my load script, the Project Name field is actually all lower case separated by an underscore. i.e. project_name. When I load it as a dimension, I make the P and N uppercase and remove the underscore.

Re: Changing Text Color Of One Field Based on Another Field Value

petter — Fri, 24 Feb 2017 19:37:05 GMT

I provided a sample app to demonstrate the concept working - so by looking at how it has been done you have to adjust it to your project.

Re: Changing Text Color Of One Field Based on Another Field Value

petter — Fri, 24 Feb 2017 19:38:52 GMT

You change/rename field names in your load script so they associate. Same field name in two tables make them connect. A key is a key in Qlik - there is no notion in Qlik itself of a foreign or primary key.

Re: Changing Text Color Of One Field Based on Another Field Value

pnn44794 — Sun, 13 Oct 2019 18:53:39 GMT

I'm sorry Petter, but I'm not understanding. In the load script of the sample you provided, there were two lines I needed to change in my load script to match yours.

Re: Changing Text Color Of One Field Based on Another Field Value

petter — Sat, 25 Feb 2017 01:17:41 GMT

The LOAD 'Test Project' & RecNo() AS [Project Name] is just a way to generate the mock-up project names because I was too lazy to fill them out one by one as a column in the data underneath... sorry about the confusion.

The OTHER table in the sample app I provided could just as well be like this:

OTHER:

LOAD * INLINE [

Project, L_Application_ID

Project Alpha, 82321

Project Nimbus, 84212

Project Omega, 95121

......

];

The Text(APP_IDs) AS APP_IDs is just to make sure that all values will be treated as text so the justification in a column is always left-justified. This does not affect the logic and it works without it too - but it doesn't look as nice.

Re: Changing Text Color Of One Field Based on Another Field Value

pnn44794 — Sun, 26 Feb 2017 21:40:25 GMT

Hello Petter:

Sorry for the delay in replying. Been packing up the house. Anyway…. Ok. I think I get it. I need to either manually map the project names to the App IDs or find an automated way to do it. I’ll work on that today and probably tomorrow. If I get them auto-mapped, would I need to change anything in the dimension Text Color and Text Format for Project Name?

Re: Changing Text Color Of One Field Based on Another Field Value

sumitjadhav — Mon, 27 Feb 2017 05:43:49 GMT

Hi Perry,

Try the below Script:

Tab1:

LOAD

[Project Name],

APP_IDs,

[# of Assets Confirmed],

[Confirmed Timeline (Qx)],

[Excluded Reason],

[In Pipeline (Y\N)],

[TOTAL VSI],

[TOTAL APPS*]

FROM

[Path);

LOAD * INLINE [

L_Applicaion_ID

22940

35415

18630

86973

88152

88524

22975

85103

81898

86909

83029

84059

22935

34961

82748

36177

89080

84231

80735

88479

85856

88152

35222

87975

14826

84329

86159

88774

84234

88703

];

Output: