4 Replies Latest reply: Dec 21, 2012 10:56 AM by allocade RSS

    QVD loading question

      I want to load a QVD and discard certain records from a group when they match a certain criteria.

       

      Lets say I have a QVD with the following table that has thousands of records loaded each month...

      Multiple record per TxID

      multiple Record types

       

       

      recID      TxID     Record Type      Date

      1            10            A            201209

      2            10            C            201211

      3            11            A            201211

      4            11            C            201209

      5            11            B            201210

      6            11            A            201109

      7            12            A            201209

       

       

      As you can see, records 3 thru 6 belong to the same TxID but record 6 has a date from a different time period.

      How do I load this QVD and discard all the records of Record Type "A" in a Tx group that have a date before the date in the record of Record Type "B" of the same transaction group.

       

      I can figure this out in SQL but I am new to Qlikview and haven't wuite got my head around this stuff yet!

       

      Thanks

        • Re: QVD loading question
          jagan mohan rao appala

           

          Hi,

           

          You need only records for 2012, then use

           

          LOAD

               *

          FROM DataSource

          WHERE Year(Date)  = 2012;

           

          HOpe this helps you.

           

          Regards,

          Jagan.

            • Re: QVD loading question

              Thanks but not quite that simple.

              What I need is to not load a record of Recort Type "A" when the date is older than the date in Record Type "B" with the same Tx group ID.

               

              So in the table below, records with recID 6 and 10  wouldn't be loaded.

               

              recID  TxID Record Type  Date
              1   10A   201209
              2   10201211
              3   11 A   201211
              4   11201209
              5   11B   201210
              6   11A   201109
              7   12A   201209
              8   12C   201210
              9   12 B   201208
              10 12 A   201207
              11   12201209
              12   13201209
                • Re: QVD loading question
                  Celambarasan Adhimulam

                  Try this

                   

                  Load * Where flag=0;

                  LOAD

                       recID, TxID, [Record Type], Date, if(Date#(Previous(Date),'YYYYMM')>Date#(Date,'YYYYMM') AND [Record Type] = 'A', 1, 0) AS flag

                  FROM QVDSource;

                   

                  Hope it helps

                    • Re: QVD loading question

                      getting there!

                      But there is no implied order. My example might imply order but that was careless on my part.

                       

                      It needs to do a lookup, if possible to get the date of the Type "B" record in a Tx group and compare the dates in the Type "A" records for that Tx group. Is it possible to compare to a temp table?

                       

                       

                      Also, why is the Record Type in you example in square brackets?

                       

                      Thanks