8 Replies Latest reply: Oct 15, 2018 10:14 PM by Edward li RSS

    Add a dimension value in load scrip

    Edward li

      Hi Guys,

       

      I'm new to QLIK Sense, could you kindly tell me if the following action can be achieved?

       

      The original data looks like:

      load * Inline

      [

      Type,Value

      A, 100

      B, 200

      ]

       

      I would like to add a Type C, and the value is the calculated result of Type A - Type B.

       

      It will be looked like:

       

      Type,Value

      A, 100

      B, 200

      C, -100

       

      Many Thanks!

       

      Edward

        • Re: Add a dimension value in load scrip
          Stefan Wühl

          Maybe like

           

          TABLE:

          load * Inline

          [

          Type,Value

          A, 100

          B, 200

          ];


          LOAD 'C' as Type,

          Rangesum(Sum(If(Type='A',Value)),-Sum(If(Type='B',Value))) as Value

          Resident TABLE;

            • Re: Add a dimension value in load scrip
              Edward li

              Hi Stefan,

               

              Thanks a lot! It works with the single table calculation!

               

              However,  can I follow up with another question? I believe it is related to where clause and data comparison

               

              When calculating multiple data with load, the sum value is duplicated as below:

               

              (My file are .dat files.)

               

               

              //////////////////////////////////////////////////////////////////////////////////////////////////


              SUB DoDir (Root)

              FOR each File in filelist(Root& '/*.dat')

              let vMR_NO_Abbr = Purgechar(SubField('$(File)','/',-1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ/.');
              let vMR_Date= Date(Date#(right('$(Root)',6),'YYYYMM'),'YYYYMM');

              ///// Root looks like this:  \Performance Review\EIMR\201805

              ///// Different month file are saved in different month folder such as \Performance Review\EIMR\201806

               

              [Table]:

              Load
              Date(Date#(right(RootName,6),'YYYYMM'),'YYYYMM') as MR.DataDate,
              Purgechar(FileName_Branch,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as Key.NO_Abbr,
              [@1] as [Function_Element],
              [@128] as [Total_Modernization],
              [@158] as [Service_Contra],
              [@161] as [Repairs],
              [@164] as [Spare_Parts];
              LOAD
              '$(Root)' as RootName,
              FileBaseName() as FileName_Branch,
              [@1],
              [@128],
              [@158],
              [@161],
              [@164]
              FROM [$(File)]
              (txt, codepage is 936, no labels, delimiter is '\t', msq, header is 3 lines)
              Where len([@1])>0 and [@1] <> 'Total' and [@1] <> 'Lead column'
              ;


              Concatenate load
              [MR.DataDate]
              ,[Key.NO_Abbr]
              ,'Total Struco' as [Function_Element]
              ,Rangesum(If([Function_Element]='Total Indirect cost',[Total_Modernization]),If([Function_Element]='Total PL Cost',[Total_Modernization])) as [Total_Modernization]

              resident [Table]
              where Key.NO_Abbr= '$(vMR_NO_Abbr)'
              ;

              NEXT File

              FOR each Dir in dirlist (Root&'/*')

              CALL DoDir(Dir)

              NEXT Dir

              END SUB

              CALL DoDir('lib://EI_MR')

              //////////////////////////////////////////////////////////////////////////////////////////////////


              All other results are fine except the value of the calculated 'Total Struco' duplicates when I load multiple month.


              I believe it is because the Concatenate load loads the previous data in Table every time in the loop.


              So I tried to wrote a where clause:

              Key.NO_Abbr= '$(vMR_NO_Abbr)' and [MR.DataDate] = '$(vMR_Date)'

              Trying to limit the Concatenate Load.

              However, if I keep the [MR.DataDate] = '$(vMR_Date)' in the script, the Concatenate Load returns no value (0 rows).


              So I tried to change it to [MR.DataDate] <= '$(vMR_Date)', so as to check my data format. The value are showed but still duplicated (as expected).

               

              Could you kindly check where I got wrong?

               

              REALLY Thanks!

               

              Kind Regards,

               

              Edward

                • Re: Add a dimension value in load scrip
                  Stefan Wühl

                  I can't see a table source for your second load, are you sure you've posted the real code?

                  (table source would be given by e.g. FROM Table or RESIDENT Table)

                   

                  Next, you've used Rangesum() instead of an aggregation function like Sum(), so you won't get the records aggregated, but keep all recods.

                    • Re: Add a dimension value in load scrip
                      Edward li

                      Thanks Stefan,

                       

                      Yep, you are right, I missed the resident Table when I copied into the board.

                       

                      It should be like:

                       

                      Concatenate load
                      [MR.DataDate]
                      ,[Key.NO_Abbr]   
                      ,'Total Struco' as [Function_Element]
                      ,Rangesum(If([Function_Element]='Total Indirect cost',[Total_Modernization]),If([Function_Element]='Total PL Cost',[Total_Modernization])) as [Total_Modernization]

                      resident [Table]
                      where Key.NO_Abbr= '$(vMR_NO_Abbr)'

                       

                      The problem I've got now is that if I put [MR.DataDate] = '$(vMR_Date)' into the where clause, the [MR.DataDate] = '$(vMR_Date)' does not work...



                    • Re: Add a dimension value in load scrip
                      Stefan Wühl

                      Maybe try comparing the date numerical values:

                       

                      let vMR_Date_num = Num(Date#(right('$(Root)',6),'YYYYMM'));


                      ....

                      where Key.NO_Abbr= '$(vMR_NO_Abbr)' and [MR.DataDate] = $(vMR_Date_num)

                      ;

                       

                      And again, if you want an aggregated table, use an aggregation function like Sum() instead of Rangesum().