topic Re: How to Use excel color code sheet with Bar Graph in QlikView

How to Use excel color code sheet with Bar Graph

vlakeshr — Tue, 19 Jun 2018 14:50:02 GMT

Hi All,

I have 7 Region and number of countries belongs to region. I have plotted Bar chart with hierarchy ( by using Drill down )

PFA screen shot. for Ba

I have uploaded excel file having color code across country and Region, I want to use that color code in bar chart

please let me know how can use loaded color code excel file in chart. PFA Excel sheet for color code

Expression: Calculating Order no.

Count({<Table={'ORFT_comment_tbl'}>}Order_Number)

I have tried Advance color code option but it's not working while I tried, .

Some body help me or please guide me how can use color code excel in chart.

Re: How to Use excel color code sheet with Bar Graph

vlakeshr — Tue, 19 Jun 2018 14:53:25 GMT

Appreciate for quick response..

I am trying solution which are available on below links but it will be not more useful because no. of Countries are more than 200

Color by dimension value

Apply Color map to all charts

Re: How to Use excel color code sheet with Bar Graph

Frank_Hartmann — Tue, 19 Jun 2018 15:32:04 GMT

Try this:

Script:

LOAD Country, 
     RGB(subfield(textbetween(Colors,'(',')'),',',1) ,subfield(textbetween(Colors,'(',')'),',',2) ,subfield(textbetween(Colors,'(',')'),',',3) ) as Colors,     
     Region, 
     Data
FROM

(ooxml, embedded labels, table is Sheet1);

Then use "Colors" in Your Backgroundexpression:

Re: How to Use excel color code sheet with Bar Graph

vlakeshr — Wed, 20 Jun 2018 07:59:14 GMT

Thanks Frank for your quick response.

I used above logic but not getting result. I have used below script in script editor

ColorCodes:

LOAD [Country Code] as Country_Code,

[Country name],

RGB(subfield(textbetween(Country_colorcode,'(',')'),',',1) ,subfield(textbetween(Country_colorcode,'(',')'),',',2) ,

subfield(textbetween(Country_colorcode,'(',')'),',',3) ) as Country_colorcode,

//Country_colorcode,

Region_original,

Region_Colorcode

FROM

[\\vsriwqlktestfil.vs.local\QlikView\QlikViewStorage\Presentation Layer\SCM\iCons\color_code.xlsx]

(ooxml, embedded labels, table is Sheet1);

and follow the same process for color code achieve but not getting result.

PFA screen shot and find the attached Excel sheet which are using.

Re: How to Use excel color code sheet with Bar Graph

vlakeshr — Wed, 20 Jun 2018 08:20:21 GMT

Hi Frank..Sorry

Your logic is working but when I concatenate with my previous table to avoid synthetic key while it's not working

below is my script

ORFT_comment_tbl:

CrossTable(Status,Value,10)

LOAD Order_Number,

MAN_Contry_Code,

Comment,

If(MAN_Contry_Code='KO','Kolumbia',ApplyMap('CountryMap',Upper(MAN_Contry_Code),'Not Defined')) as Country,

ApplyMap('RegionMap',Upper(MAN_Contry_Code),'Not Defined') as Region,

ApplyMap('Operatingcountrymap',Upper(MAN_Contry_Code),'ALL') as [Operating country],

ApplyMap('Region_originalmap',Upper(MAN_Contry_Code),'Not Defined') as Region_original,

USERID,

'ORFT_comment_tbl'as Table,

Date(DATE_Auto,'DD-MM-YYYY') as %DATE_KEY,

//TIME_Auto,

// Region,

Credit_block,

Incomplete_BOM,

Legal_block,

Order_placed_inside_DLT,

Order_placed_without_matching_forecast,

Other,

TL_ULO_not_respected,

ATP_fails,

Wrong_missing_Incoterms,

Wrong_missing_payment_terms,

Wrong_missing_price,

Wrong_missing_route,

Wrong_missing_vendor_info_record

FROM

//[\\FRIAPFIL04.EAME.SYNGENTA.ORG\Poland$\SCM\ORFT_comment_tbl.xls]

[\\vsriwqlktestfil.vs.local\QlikView\QlikViewStorage\Presentation Layer\SCM\ORFT_comment_tbl.xls]

(biff, embedded labels, table is ORFT_comment_tbl$);

ORFT_comment_tbl_1:

NoConcatenate

LOAD *

Resident ORFT_comment_tbl

Where Len(Trim(Value)) > 0;

DROP Table ORFT_comment_tbl;

Concatenate(ORFT_comment_tbl_1)

ColorCodes:

LOAD [Country Code] as Country_Code,

[Country name],

RGB(subfield(textbetween(Country_colorcode,'(',')'),',',1) ,subfield(textbetween(Country_colorcode,'(',')'),',',2) ,

subfield(textbetween(Country_colorcode,'(',')'),',',3) ) as Country_colorcode,

//Country_colorcode,

Region_original,

Region_Colorcode

FROM

[\\vsriwqlktestfil.vs.local\QlikView\QlikViewStorage\Presentation Layer\SCM\iCons\color_code.xlsx]

(ooxml, embedded labels, table is Sheet1);

Can you please let me know why it's not working while concatenate with other table to avoid Synthetic key.

I am checking the same by using resident table. let me know if you have other suggestion.

Thanks for your help to solve this issue..

Appreciate for your quick responses..

Re: How to Use excel color code sheet with Bar Graph

Frank_Hartmann — Wed, 20 Jun 2018 08:20:29 GMT

can you share your qvw? Maybe scramble sensitive fields (Settings --> Document Settings --> Scrable)

Re: How to Use excel color code sheet with Bar Graph

vlakeshr — Wed, 20 Jun 2018 09:16:13 GMT

Hi Frank..I have resolved that issue by using apply map function and it's working .

your logic is working correctly.

Thanks for quick response and correct answer...

Have a nice Day!!1

Re: How to Use excel color code sheet with Bar Graph

Frank_Hartmann — Wed, 20 Jun 2018 09:21:40 GMT

but you marked your answer as correct, not mine!

Anyway, glad it worked for you

Re: How to Use excel color code sheet with Bar Graph

vlakeshr — Wed, 20 Jun 2018 09:26:40 GMT

I have resolved an issues regarding script which was facing internally in my scripting part..

Solution which was provided by you,That is correct means your logic for color code is working correctly.

Your answer means Logic provided by Frank is correct !!!