Solved

Lookup tables with variable keys

Posted on 2013-05-24
7
222 Views
Last Modified: 2013-05-26
Is it possible to use a lookup table if the key changes? In the example below, a variable is used as the key, but it's not working because XSLT variables are not really variable.

Here is the XSL:
<lookup:NAICS_Classification_Percentage>
	<FacilityNAICS Classification="ccc" Percentage="60">Two Code Inc. Industrial Heartland 123456</FacilityNAICS>
	<FacilityNAICS Classification="ddd" Percentage="40">Two Code Inc. Industrial Heartland 456789</FacilityNAICS>
</lookup:NAICS_Classification_Percentage>

<xsl:variable name="ThisFacilityNAICS" select="concat(/Report/OrganisationNameEn, ' ', /Report/FacilityNameEn, ' ', /Report/Report_Detail/IdentificationProfile/Facility/NAICS_Codes/NAICS/NAICSCodeEn)"/>

<xsl:value-of select="$ThisFacilityNAICS"/>, <xsl:value-of select="document('')//lookup:NAICS_Classification_Percentage/FacilityNAICS[. = $ThisFacilityNAICS]/@Classification"/>, <xsl:value-of select="document('')//lookup:NPRI_GHGIS_ID/CompanyFacility[. = $ThisFacilityNAICS]/@Percentage"/>

Open in new window


Here is the XML:
<Report>
	<OrganisationNameEn>Two Code Inc.</OrganisationNameEn>
	<FacilityNameEn>Industrial Heartland</FacilityNameEn>
	<Report_Detail>
		<IdentificationProfile>
			<Facility>
				<NAICS_Codes>
                    <NAICS>
                        <NAICSCodeEn>123456</NAICSCodeEn>
                    </NAICS>
                    <NAICS>
                        <NAICSCodeEn>456789</NAICSCodeEn>
                    </NAICS>
				</NAICS_Codes>
				</Facility>
				</IdentificationProfile>
				</Report_Detail>
</Report>

Open in new window

0
Comment
Question by:mariita
  • 4
  • 3
7 Comments
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39195663
It is absolutely not clear what you want to do
But I assume you are looking for dealing with a combined key?
Wel, combined keys can easily be achieved by concatening the keys
or by having an "and" inside the predicate.

Can you explain what exactly you want to do?
0
 

Author Comment

by:mariita
ID: 39196459
Sorry, the snippet did not have enough context.

I want to loop through the list of NAICS codes in the XML file, and use a composite key (consisting of Organization, Facility, NAICS) to look up the corresponding Classification and Percentage in the lookup table. Because this facility has more than one NAICS code, the $ThisFacilityNAICS variable cannot be set to a specific value.

<lookup:NAICS_Classification_Percentage>
	<FacilityNAICS Classification="ccc" Percentage="60">Two Code Inc. Industrial Heartland 123456</FacilityNAICS>
	<FacilityNAICS Classification="ddd" Percentage="40">Two Code Inc. Industrial Heartland 456789</FacilityNAICS>
</lookup:NAICS_Classification_Percentage>

<xsl:for-each select="/Report/Report_Detail/IdentificationProfile/Facility/NAICS_Codes/NAICS">
	<xsl:variable name="ThisFacilityNAICS" select="concat(/Report/OrganisationNameEn, ' ', /Report/FacilityNameEn, ' ', /Report/Report_Detail/IdentificationProfile/Facility/NAICS_Codes/NAICS/NAICSCodeEn)"/>
	<NAICSCode>
		<NAICSClassification>
			<xsl:value-of select="document('')//lookup:NAICS_Classification_Percentage/FacilityNAICS[. = $ThisFacilityNAICS]/@Classification"/>
		</NAICSClassification>
		<Code>
			<xsl:value-of select="NAICSCodeEn"/>
		</Code>
		<ActivityPercentage>
			<xsl:value-of select="document('')//lookup:NAICS_Classification_Percentage/FacilityNAICS[. = $ThisFacilityNAICS]/@Percentage"/>
		</ActivityPercentage>
	</NAICSCode>
</xsl:for-each>

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39196686
just change the lookup

      <FacilityNAICS Classification="ccc" Percentage="60" organisation="Two Code Inc." facility="Industrial Heartland " NAICS="123456"/>

and go for

FacilityNAICS[@organisation = ...][@facility = ...][@NAICS = ...]/@Classification

no need to compound in the lookup, it is better to keep them seperated
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:mariita
ID: 39196721
I tried that but it's not working.

<lookup:NAICS_Classification_Percentage>
	<FacilityNAICS Org="Two Code Inc." Facility="Industrial Heartland " NAICS="123456" Classification="ccc" Percentage="60" />
	<FacilityNAICS Org="Two Code Inc." Facility="Industrial Heartland " NAICS="456789" Classification="ddd" Percentage="40" />
</lookup:NAICS_Classification_Percentage>

<xsl:for-each select="/Report/Report_Detail/IdentificationProfile/Facility/NAICS_Codes/NAICS">
	<xsl:value-of select="document('')//lookup:NAICS_Classification_Percentage/FacilityNAICS[@Org = /Report/OrganisationNameEn][@Facility = /Report/FacilityNameEn][@NAICS = NAICSCodeEn]/@Classification"/>, 
	<xsl:value-of select="document('')//lookup:NAICS_Classification_Percentage/FacilityNAICS[@Org = /Report/OrganisationNameEn][@Facility = /Report/FacilityNameEn][@NAICS = NAICSCodeEn]/@Percentage"/>
</xsl:for-each>

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39197035
that is a bit tricky (call it the higher science of XSLT :-)

[@Org = /Report/OrganisationNameEn]

because of the document('') the context in teh predicate is the XSLT file, no longer the source XML file. If you need to do that, you need to bind the source to a variable first

<xsl:variable name="source" select="."/>
at the top level of your stylesheet

then you can do this
[@Org = $source/Report/OrganisationNameEn]
0
 

Author Comment

by:mariita
ID: 39197398
How about the third predicate? How would I specify the context?

[@NAICS = NAICSCodeEn]
0
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 500 total points
ID: 39197405
Hi, I am just giving suggestions, so I have not looked into your stylesheet in detail (will only have time tomorrow, will visit family soon)

As soon as you start looking inside the lookup table, the context will be set to that lookup table, so outside the source XML
In order to get the old context back, you need storage in a variable
You can store the current context at every level

just store away (at the right context) the NAICSCodeEn value as $this-NAICSCodeEn
and use it in the lookup [@NAICS = $this-NAICSCodeEn]
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now