5 Replies Latest reply: Sep 16, 2010 5:35 PM by Yuri Nicolett RSS

    Set Analysis Expressions

    Yuri Nicolett

      We need to find a way to retrieve a value in a table using set analysis.

      In this table we have salary of a employee over the time, we have all the salary on each month.

      In a graph we need to show the value of salary of the last salary change.

       

      [View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.03.10.13/teste.qvw]

        • Set Analysis Expressions
          Yuri Nicolett

          A sample application is attached. Can anyone help me?

          • Set Analysis Expressions
            Yuri Nicolett

            I can post more examples if my question was confusing

            • SV:Set Analysis Expressions
              Goran Korsgren

              Hi Yuri

               

              I would not use set analysis to solve your problem.

               

              I would do something like this:

              In the load script:

              Use the Colaborador table to build a new table where each Employee has a record with a date interval specifying the salary for that period.

               

              Use that new table to get the employees salary for a given date (performing an interval match)

               

               

              • Set Analysis Expressions

                Hey Yuri.

                by you TESTE.qvw, it seems you are Brazilian as well. :)

                I have done a similar thing. But I did it on Load as suggested above:

                // date range that the value was valid
                PositionValues:
                LOAD * INLINE [
                Position, INI_KEY_DATE,FIN_KEY_DATE, salary
                1, 200801, 201001, 6
                1, 201002, 999999, 8];

                // activites done and their dates
                Activities:
                LOAD * INLINE [
                Employee, Position, Date_Activiry
                Fulano1, 1, 2010001
                Fulano1, 1, 2010007
                ];

                // join both of them
                XList_1:
                Load Postiion, INI_KEY_DATE,FIN_KEY_DATE, salary
                Resident PositionValues;
                join
                Load Employee, Position, Date_Activiry
                Resident Activities;

                // calculate wich ones are valid
                Xlist_2:
                NOCONCATENATE
                LOAD *, if((Date_Activiry>=INI_KEY_DATE and Date_Activiry<=FIN_KEY_DATE), 1, -1) AS ValidLine
                RESIDENT XList_1;

                // The final Table
                ActivitiesValue:
                Load Employee, Position, Date_Activiry, salary
                Resident Xlist_2
                Where ValidLine = 1;

                // get rid of the rest
                drop table XList_1;
                drop table XList_2;

                Just made on the Notepad, so it may have some ; or : problem. But the idea is there.

                Cheers

                Adriano