2 Replies Latest reply: May 20, 2018 5:50 AM by dion verbeke RSS

    Eliminate certain values of one data source joined to another data source

    Reema Panjwani

      Hi,

       

      I am fairly new to qlik sense and right now I am trying to create a visualization with two data sources one from an excel file and another from cloudera.

       

      I have joined the two data sources with a unique identifier but in my visualization I only want to show the unique identifier from the excel file and not from cloudera. More like a vlookup function.

       

      Is there a way to do so?

       

      Thank you!

        • Re: Eliminate certain values of one data source joined to another data source
          Mark Perreault

          My understanding is that the data from Cloudera and Excel have either been concatenated or joined into one table.  Prior to doing this you can create a Source or Flag field which can be used in Set Analysis.

          Ex.


          FactTable:

          Load *,

          'Cloudera' as Source

          From ClouderaSource;

           

          Concatenate(Cloudera)

          Load *,

          'Excel' as Source

          From ExcelSource;

           

          Then in your visualization you can you do count({<[Source] = {'Excel'}>}Field)

          etc.

            • Re: Eliminate certain values of one data source joined to another data source
              dion verbeke

              Several strategies are possible:

               

              1. Do preprocessing with another tool or a in a database. This is clear and you can switch toolset later.

              2. Adding a Flag as mentioned before: Source = 'E' or Source = 'C'. This is ok, but generally results in complicated set analysis and you might regret your decision.

              3. Do both calculations as 2 seperate fields:

                  

              Excel:

              Id    as Id_Excel

              Id

              Amount as Amount_Excel

               

              Cloudera:

              Id

              Id as Id_Cloudera

              Amount as Amount_Cloudera

               

              It will still join correctly, but you now have the choice to take any calculation you want.

               

              Count(Id_Excel)

              Count(Id)

              Count(Id_Cloudera)

               

              For performance and clarity usually the last one is preferred.

               

              Dion