How to Use excel color code sheet with Bar Graph

[vlakeshr]

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.

[vlakeshr]

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

[Frank_Hartmann]

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:

[vlakeshr]

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.

[vlakeshr]

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..

[Frank_Hartmann]

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

[vlakeshr · Accepted solution]

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

[Frank_Hartmann]

but you marked your answer as correct, not mine!

Anyway, glad it worked for you

[vlakeshr]

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 !!!