6 Replies Latest reply: Jan 28, 2016 10:11 PM by ADFRIANSYAH RYAN RSS

    Expression

      Hi all master Qlikview

       

      I have problem with qlickview

       

      I have table from SAP like that

       

      KDMAT                                  VBELN           POSNR

      20086689                               1111000              010

      70000333                               1199922              010

      000000000076783954              1540000             020

      PRTS897667                           2920002             030

       

      i want to check string from field KDMAT, if the length is 8 and only contain number

      i want to concatenate this value with 0000000000

       

      so this field become 000000000020086689

      and modify and store to new QVD

       

      Next Table like that :

       

      KDMAT                                 VBELN           POSNR

      000000000020086689             1111000             010

      000000000070000333             1199922             010

      000000000076783954             1540000             020

      PRTS897667                          2920002             030

       

      Please help me master

       

      ry88

        • Re: Expression
          Marcus Sommer

          Try something like this:

           

          if(len(KDMAT)=8 and isnum(KDMAT), '0000000000' & KDMAT, KDMAT) as KDMAT

           

          - Marcus

            • Re: Expression
              Stefan Wühl

              Marcus, I am unsure if this will change the text representation for an existing numeric value, so maybe we need something like

               

              if(len(KDMAT)=8 and isnum(KDMAT), Num(KDMAT,'000000000000000000'), KDMAT) as KDMAT

               

              or

               

              if(len(KDMAT)=8 and isnum(KDMAT), Text( '0000000000' & KDMAT), KDMAT) as KDMAT

                • Re: Expression
                  Marcus Sommer

                  After your remark I'm not sure how qv will interpret this - normally a concat between two values results in a string but fields will be normally interpreted by the format from the very first loading-value of those field - therefore I will make a test:

                   

                  Load if(len(KDMAT)=8 and isnum(KDMAT), '0000000000' & KDMAT, KDMAT) as KDMAT inline [

                  KDMAT

                  1

                  20086689

                  70000333

                  000000000076783954

                  PRTS897667

                  20086689

                  ];

                   

                  and result is:

                   

                   

                  Therefore the simple string-concat seems to work, but your solutions ensure that the output is in the wanted format.

                   

                  - Marcus

                    • Re: Expression
                      Stefan Wühl

                      I think there are two things to look at in your example:

                       

                      I don't think that the Format of the KDMAT values have been set before the Input records get into your if() statement, I was more thinking about a resident load, where the same numeric value has been loaded before.

                       

                      Second, I forgot that QV will not interpret strings with more than 14 Digits as numbers, so this specific format will always result in a string. But what if we just add 4 leading zeros?

                       

                      So just out of curiosity (because it's not the scenario the OP described), try to run this:

                       


                      T:
                      Load if(len(KDMAT)=8 and isnum(KDMAT), '0000' & KDMAT, KDMAT) as KDMAT, KDMAT as KDMAT2 inline [
                      KDMAT
                      1
                      20086689
                      70000333
                      000000000076783954
                      PRTS897667
                      20086689
                      ]
                      ;


                      LOAD KDMAT, if(len(KDMAT2)=8 and isnum(KDMAT2), '0000' & KDMAT2, KDMAT2) as KDMAT2
                      Resident T;

                        • Re: Expression
                          Marcus Sommer

                          With Inline-Load and four zeros the result looked:

                           

                           

                          With resident-load the leading zeros are gone:

                           

                           

                          I also tested the num() approach with resident and leading zeros didn't appear so that only your second text() approach worked. This meant one must be very carefully by handling leading zeros.

                           

                          - Marcus

                  • Re: Expression

                    marcus_sommer thanks a lot master for advice

                    swuehl your advice very helpful

                     

                    I have tried all advice and worked in my qlikview

                     

                     

                     

                    ry88