3 Replies Latest reply: Jun 21, 2009 2:31 PM by Shumail Hussain RSS

    How to identify line break

    Shumail Hussain

      I am parsing a data from a text file and reading two line as a single record for QlikView. I wanted to find out the line break in text file because the length of the last field in the first line of the record varies.

      Can any one please help me or I have to use if condition for this?

       



      $(CurrentFileName):
      LOAD
      // Line 1 of the text file
      @1:2T as [PH]
      ,@3:21T as [CD_NO]
      ,@6:19T as [MID_14]
      ,@20:20T as [Replacement]
      ,@22:24T as [PH_FILLER1]
      ,@25:54T as [FIRST_NAME]
      ,@55:84T as [MIDDL_NAME]
      ,@85:114T as [LAST_NAME]
      ,@115:133T as [NATIONALITY]
      ...............
      // Line 2 of the text file
      @587:588T as [CH],
      @589:607T as [CH_CD_NO],
      @608:633T as [EMBOSSING_NAME],
      @634:642T as [C_LIMIT],
      @643:645T as [CURRENCY],
      @646:649T as [AUTH_STS],
      @650:652T as [D_DAYS],
      @653:653T as [FILLER_CH1],
      @654:657T as [VALID_FRMDAT],

      FROM [$(Path)$(XHB.txt)] (ansi, fix, no labels, header is line, record is 2 lines);


        • How to identify line break
          Shumail Hussain

          There is not fixed / delimation for the line break but i can identify the postion of break through the value of field CH which always contains the Value CH000 and there is no anyother field present which contain the same value so i can identify the position through this field but i dont know how to do this qlikview. Below is the code which i was trying to identify the position next line. Please help

           


          @&(index(@1:1330T,'CH000')):(index(@1:1330T,'CH000')+2)T as [CH], // X(02)
          @&(index(@1:1330T,'CH000')+2):(index(@1:1330T,'CH000')+21)T as [CH_CD_NO],



            • How to identify line break
              YVES BLAKE

              If you just need to separate in two rows, this will work fine :

              data:
              load
              subfield(raw_text,'CH000',1) as row1,
              subfield(raw_text,'CH000',1) as row2

              ;

              LOAD * INLINE [
              raw_text
              this_is_variable_textCH000this_is_secondline
              ];

                • How to identify line break
                  Shumail Hussain

                  Thanks Yves, I think no one couldnt understand my requirement. But i got a clue from Yves code. Below is the required code which i was expecting.

                   

                  <pre>
                  $(CurrentFileName):
                  LOAD // Line 1 of the text file
                  @1:2T as [PH]
                  ,@3:21T as [CD_NO]
                  ,@6:19T as [MID_14]
                  ,@20:20T as [Replacement]
                  ,@22:24T as [PH_FILLER1]
                  ,@25:54T as [FIRST_NAME]
                  ,@55:84T as [MIDDL_NAME]
                  ,@85:114T as [LAST_NAME]
                  ,@115:133T as [NATIONALITY]
                  ............... // Line 2 of the text filemid(@1:1330T,index(@1:1330T,'CH000'),2) as [CH],// X(02)

                  mid(@1:1330T,index(@1:1330T,'CH000')+2,19) as [CH_CD_NO],// X(19) CARD NUMBER
                  mid(@1:1330T,index(@1:1330T,'CH000')+21,26) as [EMBOSSING_NAME],// X(26) EMBOSSING NAME
                  mid(@1:1330T,index(@1:1330T,'CH000')+47,9) as [C_LIMIT],// 9(09) CARD LEVEL LIMIT
                  mid(@1:1330T,index(@1:1330T,'CH000')+56,3) as [CURRENCY],// X(03) BILLING CURRENCY
                  mid(@1:1330T,index(@1:1330T,'CH000')+59,4) as [AUTH_STS],// X(04) AUTORIZATION STATUS
                  mid(@1:1330T,index(@1:1330T,'CH000')+63,3) as [D_DAYS],// 9(03) DAYS DELINQUENCY
                  mid(@1:1330T,index(@1:1330T,'CH000')+66,1) as [FILLER_CH1],// X(01)
                  mid(@1:1330T,index(@1:1330T,'CH000')+67,4) as [VALID_FRMDAT] // 9(04) EXPIRY DATE - VALID FROM

                  FROM [$(Path)$(XHB.txt)] (ansi, fix, no labels, header is line, record is 2 lines);