Grouping country names and show region wise

[#1]

Hi, I have an oracle database showing the ticket numbers and country names where the tickets are opened. Now I need to show them in qlikview as region wise graph. The regions I have are America's, North, South Europe, APAC. I have to map the countries in the data base to these regions and show a region wise graph showing number of tickets opened. I saved the country names and region names in an excel and used the below code, but unable to link the data:

SQL Select

"fact ticket"."incident number", "fact ticket"."country"

FROM

"fact ticket" (this is Oracle database)

LEFT JOIN

LOAD country,

     region

FROM

(ooxml, embedded labels, table is Sheet1);

[adamdavi3s]

Arun,

Please can you let us know what error it is you are getting and possibly provide a small sample application?

[vinieme12]

Your fields will be linked automatically if you have the SAME FieldNames

Your country field from Oracle Load  should have the same name as your mapping table

FACT:

Load

[fact ticket.incident number] as IncidentNumber,

[fact ticket.country] as country

SQL Select

"fact ticket"."incident number", "fact ticket"."country"

FROM

"fact ticket" (this is Oracle database)

LEFT JOIN

LOAD country,

     region

  FROM

(ooxml, embedded labels, table is Sheet1);

[#4]

Hi Adam,

I am no receiving any error. Script is executing but the data is not getting linked.

Thanks,

Arun

[adamdavi3s]

Hi Arun,

As Vineeth says, I imagine it is your load statement not linking the data correctly

[Colin-Albert]

No need to join the data use a mapping table.

Try this - not the mapping table is loaded first and is deleted at the end of the load script automatically.

(Edit - missing semicolon before SQL Select added)

REGION_MAP:

MAPPING LOAD

     country,

     region

  FROM

(ooxml, embedded labels, table is Sheet1);

FACT:

Load

[fact ticket.incident number] as IncidentNumber,

[fact ticket.country] as country,

AplyMap('REGION_MAP',  [fact ticket.country], 'N/A') as region

;

SQL Select

"fact ticket"."incident number", "fact ticket"."country"

FROM

"fact ticket" (this is Oracle database)

[#7]

Hi,

I tried using the above script but received the following error message when the script is executed:

Field not found - <"fact ticket"."incident number">

SQL SELECT

"fact ticket"."incident number", "fact ticket".country

FROM ITICS."Fact Ticket"

Please assist.

[adamdavi3s · Accepted solution]

You need to keep the names consistent throughout. try this

REGION_MAP:

MAPPING LOAD

     Country,

     Region

  FROM

(ooxml, embedded labels, table is Sheet1);

FACT:

Load

[IncidentNumber] as IncidentNumber,

[Country] as Country,

AplyMap('REGION_MAP',  [Country], 'N/A') as Region

;

SQL Select

"fact ticket"."incident number" as "IncidentNumber" , "fact ticket"."country" as "Country"

FROM

"fact ticket"

[#9]

Hi,

Still I receive the same error. I think there is some error in load statement. If I comment the below statements the script is running successfully.

//Load

//[IncidentNumber] as IncidentNumber,

//[Country] as Country,

//AplyMap('REGION_MAP',  [Country], 'N/A') as Region;

But if I uncomment them, again I receive the error like below:

Field not found - <"fact ticket"."incident number">

SQL SELECT

"fact ticket"."incident number", "fact ticket".country

FROM ITICS."Fact Ticket"

Thanks,

Arun

[adamdavi3s]

This is just a basic Qlik error I think, you just need to get all your field names sorted out to make sure it works.

I am not that familiar with Oracle SQL I am afraid