Search code examples
xmlxsltxslt-2.0

XSLT: How to access element from previous position

I have the following input xml file:

<?xml version='1.0' encoding='UTF-8'?>
<root>
    <row>
        <userId>40668825871</userId>
        <Cargo>ANL CONTR QUALIDADE SR</Cargo>
        <Atuacao>Interno</Atuacao>
        <Nivel>PROF</Nivel>
        <TipoCargo></TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>BRAINFARMA IND. QUIMICA E FARMACEUTICA S.A.</Empresa>
        <GrupoLotacao>BRAINFARMA - ANAPOLIS</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>P&amp;D E QUALIDADE</Unidade>
        <DataInicio>01/01/2023</DataInicio>
        <DataFim>01/08/2023</DataFim>
        <MotivoEvento>Alteração de Reporte</MotivoEvento>
        <Programa>180</Programa>
    </row>
    <row>
        <userId>40668825871</userId>
        <Cargo>ANL CONTR QUALIDADE SR</Cargo>
        <Atuacao>Interno</Atuacao>
        <Nivel>PROF</Nivel>
        <TipoCargo></TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>BRAINFARMA IND. QUIMICA E FARMACEUTICA S.A.</Empresa>
        <GrupoLotacao>BRAINFARMA - ANAPOLIS</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>P&amp;D E QUALIDADE</Unidade>
        <DataInicio>01/09/2023</DataInicio>
        <DataFim>01/31/2023</DataFim>
        <MotivoEvento>Alteração de Horário</MotivoEvento>
        <Programa>180</Programa>
    </row>
    <row>
        <userId>40668825871</userId>
        <Cargo>ANL CONTR QUALIDADE SR</Cargo>
        <Atuacao>Interno</Atuacao>
        <Nivel>PROF</Nivel>
        <TipoCargo></TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>BRAINFARMA IND. QUIMICA E FARMACEUTICA S.A.</Empresa>
        <GrupoLotacao>BRAINFARMA - ANAPOLIS</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>P&amp;D E QUALIDADE</Unidade>
        <DataInicio>02/01/2023</DataInicio>
        <DataFim>02/28/2023</DataFim>
        <MotivoEvento>Transferência de CDC</MotivoEvento>
        <Programa>180</Programa>
    </row>
    <row>
        <userId>40668825871</userId>
        <Cargo>ANL VALIDACAO SR</Cargo>
        <Atuacao>Interno</Atuacao>
        <Nivel>PROF</Nivel>
        <TipoCargo></TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>BRAINFARMA IND. QUIMICA E FARMACEUTICA S.A.</Empresa>
        <GrupoLotacao>BRAINFARMA - ANAPOLIS</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>P&amp;D E QUALIDADE</Unidade>
        <DataInicio>03/01/2023</DataInicio>
        <DataFim>05/05/2023</DataFim>
        <MotivoEvento>Transferência de Estrutura</MotivoEvento>
        <Programa>180</Programa>
    </row>
    <row>
        <userId>40668825871</userId>
        <Cargo>ANL VALIDACAO SR</Cargo>
        <Atuacao>Interno</Atuacao>
        <Nivel>PROF</Nivel>
        <TipoCargo></TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>BRAINFARMA IND. QUIMICA E FARMACEUTICA S.A.</Empresa>
        <GrupoLotacao>BRAINFARMA - ANAPOLIS</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>P&amp;D E QUALIDADE</Unidade>
        <DataInicio>05/06/2023</DataInicio>
        <DataFim>12/31/2023</DataFim>
        <MotivoEvento>Alteração de Dados</MotivoEvento>
        <Programa>180</Programa>
    </row>
    <row>
        <userId>34916921801</userId>
        <Cargo>COORD DESENV FARMACOTECNICO</Cargo>
        <Atuacao>Interno</Atuacao>
        <Nivel>COORD</Nivel>
        <TipoCargo></TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>BRAINFARMA IND. QUIMICA E FARMACEUTICA S.A.</Empresa>
        <GrupoLotacao>BRAINFARMA - BARUERI</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>P&amp;D E QUALIDADE</Unidade>
        <DataInicio>08/01/2022</DataInicio>
        <DataFim>01/31/2023</DataFim>
        <MotivoEvento>Transferência de Estrutura</MotivoEvento>
        <Programa>180</Programa>
    </row>
    <row>
        <userId>34916921801</userId>
        <Cargo>COORD DESENV EMBALAGENS</Cargo>
        <Atuacao>Interno</Atuacao>
        <Nivel>COORD</Nivel>
        <TipoCargo></TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>BRAINFARMA IND. QUIMICA E FARMACEUTICA S.A.</Empresa>
        <GrupoLotacao>BRAINFARMA - BARUERI</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>P&amp;D E QUALIDADE</Unidade>
        <DataInicio>02/01/2023</DataInicio>
        <DataFim>02/28/2023</DataFim>
        <MotivoEvento>1-Enquadramento de cargo</MotivoEvento>
        <Programa>180</Programa>
    </row>
    <row>
        <userId>34916921801</userId>
        <Cargo>COORD DESENV EMBALAGENS</Cargo>
        <Atuacao>Interno</Atuacao>
        <Nivel>COORD</Nivel>
        <TipoCargo></TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>BRAINFARMA IND. QUIMICA E FARMACEUTICA S.A.</Empresa>
        <GrupoLotacao>BRAINFARMA - BARUERI</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>P&amp;D E QUALIDADE</Unidade>
        <DataInicio>03/01/2023</DataInicio>
        <DataFim>05/05/2023</DataFim>
        <MotivoEvento>Transferência de Estrutura</MotivoEvento>
        <Programa>180</Programa>
    </row>
    <row>
        <userId>34916921801</userId>
        <Cargo>COORD DESENV EMBALAGENS</Cargo>
        <Atuacao>Interno</Atuacao>
        <Nivel>COORD</Nivel>
        <TipoCargo></TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>BRAINFARMA IND. QUIMICA E FARMACEUTICA S.A.</Empresa>
        <GrupoLotacao>BRAINFARMA - BARUERI</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>P&amp;D E QUALIDADE</Unidade>
        <DataInicio>05/06/2023</DataInicio>
        <DataFim>12/31/2023</DataFim>
        <MotivoEvento>Alteração de Dados</MotivoEvento>
        <Programa>180</Programa>
    </row>
    <row>
        <userId>45787737873</userId>
        <Cargo>PROPAGANDISTA JR</Cargo>
        <Atuacao>Campo</Atuacao>
        <Nivel>PROF</Nivel>
        <TipoCargo></TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>HYPERA S.A</Empresa>
        <GrupoLotacao>HYPERA - CD GOIANIA</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>VENDAS E MARKETING</Unidade>
        <DataInicio>01/01/2023</DataInicio>
        <DataFim>06/30/2023</DataFim>
        <MotivoEvento>Alteração de Reporte</MotivoEvento>
        <Programa>180</Programa>
    </row>
    <row>
        <userId>45787737873</userId>
        <Cargo>PROPAGANDISTA JR</Cargo>
        <Atuacao>Campo</Atuacao>
        <Nivel>PROF</Nivel>
        <TipoCargo></TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>HYPERA S.A</Empresa>
        <GrupoLotacao>HYPERA - CD GOIANIA</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>VENDAS E MARKETING</Unidade>
        <DataInicio>07/01/2023</DataInicio>
        <DataFim>07/31/2023</DataFim>
        <MotivoEvento>Alteração de Dados</MotivoEvento>
        <Programa>180</Programa>
    </row>
    <row>
        <userId>45787737873</userId>
        <Cargo>PROPAGANDISTA JR</Cargo>
        <Atuacao>Campo</Atuacao>
        <Nivel>PROF</Nivel>
        <TipoCargo>OPERACIONAL</TipoCargo>
        <Contrato>CLT</Contrato>
        <Empresa>HYPERA S.A</Empresa>
        <GrupoLotacao>HYPERA - CD GOIANIA</GrupoLotacao>
        <Divisao>FARMA</Divisao>
        <Unidade>VENDAS E MARKETING</Unidade>
        <DataInicio>08/01/2023</DataInicio>
        <DataFim>12/31/2023</DataFim>
        <MotivoEvento>Alteração de Dados</MotivoEvento>
        <Programa>180</Programa>
    </row>
</root>

It's basically the periods that an employee worked in the current year, where each row represents the period in a specific job (an employee can have one or more jobs/rows).

I'm able to group by employee with the following XSLT, but now I need to compare the dates between the current row and the previous one (DataInicio and DataFim) to generate the output xml.

How I can do that? I have tried many things, but no one have worked.

Here it's the XSLT (the line where I try to create the variable datainicio_ant to store the field DataInicio from previous row it's not working). Could please someone help me?

<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:hci="http://sap.com/it/" exclude-result-prefixes="hci"
    xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:xs="http://www.w3.org/2001/XMLSchema">

    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

    <xsl:template match="/">

        <Colaboradores>

            <xsl:for-each-group select="root/row/userId" group-by="text()">

                <Colaborador>
                    <externalCode>
                        <xsl:value-of select="../userId"/>
                    </externalCode>
                    
                    <!-- A data efetiva deve ser o último dia do ano de apuração -->
                    <effectiveStartDate>12/31/<xsl:value-of select="substring(..[1]/DataFim, 7, 4)"/></effectiveStartDate>

                    <xsl:for-each select="current-group()">

                        <xsl:sort select="../DataFim"/>
                        
            <!-- HERE IT'S THE ERROR, HOW CAN I CREATE A VARIABLE WITH THE VALUE OF DataInicio FROM PREVIOUS RECORD?            
            <xsl:variable name="datainicio_ant" select="preceding-sibling::..[1]/DataInicio"/>  -->

                        <xsl:choose>
                            <xsl:when test="position() = 1">
                                <cust_cargo1>
                                    <xsl:value-of select="../Cargo"/>
                                </cust_cargo1>
                                <cust_Data_inic_cargo1>
                                    <xsl:value-of select="../DataInicio"/>
                                </cust_Data_inic_cargo1>
                                <cust_Data_fim_cargo1>
                                    <xsl:value-of select="../DataFim"/>
                                </cust_Data_fim_cargo1>
                           
                            </xsl:when>

                            <xsl:when test="position() = 2">
                               <cust_cargo2>
                                    <xsl:value-of select="../Cargo"/>
                                </cust_cargo2>
                                <cust_Data_inic_cargo2>
                                    <xsl:value-of select="../DataInicio"/>
                                </cust_Data_inic_cargo2>
                                <cust_Data_fim_cargo2>
                                    <xsl:value-of select="../DataFim"/>
                                </cust_Data_fim_cargo2>

                            </xsl:when>

                        </xsl:choose>

                    </xsl:for-each>

                </Colaborador>

            </xsl:for-each-group>

        </Colaboradores>

    </xsl:template>

</xsl:stylesheet>

I just need to fix this peace of the code, the rest it's working fine:

        \<!-- HERE IT'S THE ERROR, HOW CAN I CREATE A VARIABLE WITH THE VALUE OF DataInicio FROM PREVIOUS RECORD?           
        \<xsl:variable name="datainicio_ant" select="preceding-sibling::..\[1\]/DataInicio"/\> -->

EDIT 2: I tried to apply Martin suggestion, but it's not working yet:

<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:hci="http://sap.com/it/" exclude-result-prefixes="hci"
xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">
    <Colaboradores>
       <xsl:for-each-group select="root/row/userId" group-by="text()">
            <Colaborador>
                <externalCode>
                    <xsl:value-of select="../userId"/>
                </externalCode>

                <!-- A data efetiva deve ser o último dia do ano de apuração -->
                <effectiveStartDate>12/31/<xsl:value-of select="substring(..[1]/DataFim, 7, 4)"/></effectiveStartDate>
                <xsl:for-each select="current-group()">                 
                <xsl:sort select="../DataFim"/> 

                <!--  BEGIN -->
                <xsl:variable name="pos" select="position()"/>             
                <xsl:variable name="group" select="current-group()"/>                   
                <xsl:variable name="datainicio_ant" select="$group[$pos - 1]/DataInicio"/>                    
                <!--  END -->

                    <xsl:choose>
                        <xsl:when test="position() = 1">
                            <cust_cargo1>
                                <xsl:value-of select="../Cargo"/>
                            </cust_cargo1>
                            <cust_Data_inic_cargo1>
                                <xsl:value-of select="../DataInicio"/>
                            </cust_Data_inic_cargo1>
                            <cust_Data_fim_cargo1>
                                <xsl:value-of select="../DataFim"/>
                            </cust_Data_fim_cargo1>

                        </xsl:when>

                        <xsl:when test="position() = 2">
                           <cust_cargo2>
                                <xsl:value-of select="../Cargo"/>
                            </cust_cargo2>
                            <cust_Data_inic_cargo2>
                                <xsl:value-of select="../DataInicio"/>
                            </cust_Data_inic_cargo2>
                            <cust_Data_fim_cargo2>
                                <xsl:value-of select="../DataFim"/>
                            </cust_Data_fim_cargo2>
                            <!--    BEGIN -->
                            <teste>
                            <xsl:value-of select="$datainicio_ant"/>
                            </teste>
                            <!--    END -->
                        </xsl:when>
                    </xsl:choose>
                </xsl:for-each>
            </Colaborador>
        </xsl:for-each-group>
    </Colaboradores>
</xsl:template>

</xsl:stylesheet>

Solution

  • See if this minimal example can get you on the right track:

    XML

    <root>
    <row>
        <userId>1</userId>
        <value>1a</value>
    </row>
    <row>
        <userId>2</userId>
        <value>2a</value>
    </row>
    <row>
        <userId>1</userId>
        <value>1b</value>
    </row>
    <row>
        <userId>2</userId>
        <value>2b</value>
    </row>
    <row>
        <userId>1</userId>
        <value>1c</value>
    </row>
</root>

    XSLT 2.0

    <xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/root">
    <output>
        <xsl:for-each-group select="row" group-by="userId">
            <group userId="{current-grouping-key()}">
                <xsl:for-each select="current-group()">
                    <xsl:variable name="i" select="position()" />
                    <item>
                        <xsl:copy-of select="value"/>
                        <prev-value>
                            <xsl:value-of select="current-group()[$i - 1]/value"/>
                        </prev-value>
                    </item>
                </xsl:for-each>
            </group>
        </xsl:for-each-group>
    </output>
</xsl:template>

</xsl:stylesheet>

    Result

    <?xml version="1.0" encoding="UTF-8"?>
<output>
   <group userId="1">
      <item>
         <value>1a</value>
         <prev-value/>
      </item>
      <item>
         <value>1b</value>
         <prev-value>1a</prev-value>
      </item>
      <item>
         <value>1c</value>
         <prev-value>1b</prev-value>
      </item>
   </group>
   <group userId="2">
      <item>
         <value>2a</value>
         <prev-value/>
      </item>
      <item>
         <value>2b</value>
         <prev-value>2a</prev-value>
      </item>
   </group>
</output>

    Added:

    Let's also consider a more complicated case, where we want to sort the items of the current group and get the value of the preceding item in the sorted sequence:

    XML

    <root>
    <row>
        <userId>1</userId>
        <value>1c</value>
    </row>
    <row>
        <userId>2</userId>
        <value>2b</value>
    </row>
    <row>
        <userId>1</userId>
        <value>1b</value>
    </row>
    <row>
        <userId>2</userId>
        <value>2a</value>
    </row>
    <row>
        <userId>1</userId>
        <value>1a</value>
    </row>
</root>

    XSLT 2.0

    <xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/root">
    <output>
        <xsl:for-each-group select="row" group-by="userId">
            <group userId="{current-grouping-key()}">
                <xsl:variable name="sorted-group" as="element()*">
                    <xsl:perform-sort select="current-group()">
                        <xsl:sort select="value"/>
                    </xsl:perform-sort> 
                </xsl:variable> 
                <xsl:for-each select="$sorted-group">
                    <xsl:variable name="i" select="position()" />
                    <item>
                        <xsl:copy-of select="value"/>
                        <prev-value>
                            <xsl:value-of select="$sorted-group[$i - 1]/value"/>
                        </prev-value>
                    </item>
                </xsl:for-each>
            </group>
        </xsl:for-each-group>
    </output>
</xsl:template>

</xsl:stylesheet>

    Result

    <?xml version="1.0" encoding="UTF-8"?>
<output>
   <group userId="1">
      <item>
         <value>1a</value>
         <prev-value/>
      </item>
      <item>
         <value>1b</value>
         <prev-value>1a</prev-value>
      </item>
      <item>
         <value>1c</value>
         <prev-value>1b</prev-value>
      </item>
   </group>
   <group userId="2">
      <item>
         <value>2a</value>
         <prev-value/>
      </item>
      <item>
         <value>2b</value>
         <prev-value>2a</prev-value>
      </item>
   </group>
</output>