4 Replies Latest reply: Dec 2, 2016 1:10 AM by Tresesco B RSS

    Manipulate binary load table

    Tian Kian Tan

      Hi guys , i am creating a qlikview script to add additional column for data categorization to a existing table from qvw file.

       

      heres the script:

       

      Binary C:\Users\tan\Desktop\Data Model\DATA_MODEL.qvw;

      Rename table FACTS to FACTS_temp;

      [FACTS]:

      load *,

      if(IsText(mid(fin_accnt_code,8,1)) and fin_gl_line_allocation=' ',fin_gl_line_due_datetime - today()) as [Aged Days],

      if([Aged Days]<0,'Current',

           if([Aged Days]<=30,'1-30',

                if([Aged Days]<=60,'31-60',

                if([Aged Days]<=90,'61-90',

                if([Aged Days]<=180,'91-180',

                if([Aged Days]<=365,'181-365','365+'))))))

                  as Category

      Resident [FACTS_temp];

       

      drop table facts_temp;

       

      But the above script does not work as Aged Days is not created yet, is there any other way to do this?

        • Re: Manipulate binary load table
          Tresesco B

          Try with preceding load like:

           

          Binary C:\Users\tan\Desktop\Data Model\DATA_MODEL.qvw;

          Rename table FACTS to FACTS_temp;

          [FACTS]:

          load *,

          if([Aged Days]<0,'Current',

               if([Aged Days]<=30,'1-30',

                    if([Aged Days]<=60,'31-60',

                    if([Aged Days]<=90,'61-90',

                    if([Aged Days]<=180,'91-180',

                    if([Aged Days]<=365,'181-365','365+'))))))

                      as Category ;

           

          load *,

          if(IsText(mid(fin_accnt_code,8,1)) and fin_gl_line_allocation=' ',fin_gl_line_due_datetime - today()) as [Aged Days]

          Resident [FACTS_temp];

           

          drop table facts_temp;

          • Re: Manipulate binary load table
            Sushil Kumar

            Try this:

             

             

            Binary C:\Users\tan\Desktop\Data Model\DATA_MODEL.qvw;

            Rename table FACTS to FACTS_temp;

            [FACTS]:

            load *,

            if([Aged Days]<0,'Current',

                 if([Aged Days]<=30,'1-30',

                      if([Aged Days]<=60,'31-60',

                      if([Aged Days]<=90,'61-90',

                      if([Aged Days]<=180,'91-180',

                      if([Aged Days]<=365,'181-365','365+'))))))

                        as Category;

             

            load *,

            if(IsText(mid(fin_accnt_code,8,1)) and fin_gl_line_allocation=' ',fin_gl_line_due_datetime - today()) as [Aged Days],

            Resident [FACTS_temp]

            • Re: Manipulate binary load table
              Tian Kian Tan

              that works, thanks guys but is there any other way to prevent loading the whole table twice? its a really huge table