8 Replies Latest reply: Nov 10, 2017 2:56 AM by Ivan Bozov RSS

    How to load this data

    Adrian Ferrer

      Hi everyone,

       

      I am trying to figure out how to properly load this data. So I have these two sample tables to illustrate:

       

      Sales (this is like a unit level table)

      ColorMonthCountGroup
      RedJan10G1
      RedFeb20G2
      BlueJan30G1

       

      Target

      ColorMonthTarget Count
      RedJan50
      RedFeb50
      RedMar50
      BlueJan50

       

      Now, I want to display these in a pivot table as:

      ColorMonthTarget (based on Color/Month)ActualSum of Target (based on Month)Actual
      RedJan501010040
      Feb50205020
      Mar500500
      BlueJan503010040

       

       

      And If I do a filter on Group = G1, this is what should be displayed:

      ColorMonthTarget (based on Color/Month)ActualSum of Target (based on Month)Actual
      RedJan501010040
      Feb500500
      Mar500500
      BlueJan503010040

       

       

      Thanks in advance!

        • Re: How to load this data
          Shraddha Gajare

          in Load script,

          link both tables using composite key of Color and Month fields.

          For Ex.  Color &'-'& Month as Key.

          This you will have to create in both tables (Sales & Target)

          • Re: How to load this data
            Ivan Bozov

            Hello! Load it like this, Qlik is going to automatically link the tables based on Color and Month:

            
            Sales:
            Load
                 Color,
                 Month,
                 Count,
                 Group
            Inline
            [
            Color,Month,Count,Group
            Red,Jan,10,G1
            Red,Feb,20,G2
            Blue,Jan,30,G1
            ]
            (Delimiter is ',');
            
            
            Target:
            Load
                 Color,
                 Month,
                 [Target Count]
            Inline
            [
            Color,Month,Target Count
            Red,Jan,50
            Red,Feb,50
            Red,Mar,50
            Blue,Jan,50
            ]
            (Delimiter is ',');
            
            
            
            
            • Re: How to load this data
              Adrian Ferrer

              Thanks, but doing that will create a synthetic key, which can be solved by making a composite key.

               

              The problem is, the "Red-March" comination is not in  the Sales table, hence i cannot achieve this(emphasis on third row):

              ColorMonthTarget (based on Color/Month)ActualSum of Target (based on Month)Actual
              RedJan501010040
              Feb50205020
              Mar500500
              BlueJan503010040
                • Re: How to load this data
                  Ivan Bozov

                  If you see a synthetic key in your data model and you did not expect it, then you most probably have a problem. If you know why it is there, you are good to go.


                  If you use the script that I posted you should be able to get the table in the format that you need it. Since there is no data for the combination Red-March, the Actual will show "-". You can avoid that by changing the expression, e.g. =If(Sum(Count)>0, Sum(Count), '0').