8 Replies Latest reply: Jan 11, 2011 1:01 PM by Anil Konduri RSS

    Help with Load Script -

    Anil Konduri

      Hi All,

      I have attached a image, which contains the format of my data in .csv file, please help me, how to load these data into QV, with and without test column.

       

      Thanks in Advance

        • Help with Load Script -
          Michael Robertshaw

          See 'crosstable' in the QlikView Help.

          • Re. :Help with Load Script -
            Martin FAVIER

            Hi,

            You have to develop something like this or send your file to adapt it :

             



            temp:
            CrossTable(Month, Data)
            LOAD *
            FROM $(Path)\$(File).xlsx (ooxml, embedded labels, table is Feuil1);

            temp2:
            Load
            Prod,
            if(isnum(mid(Month,2))=False() and len(trim(Month))>1,Month,peek(Month)) as Month,
            Data,
            recno() as recno
            Resident temp
            ;

            DROP TABLE temp;

            temp3:
            Load Prod,
            Month,
            tmpType1,
            if(len(trim(tmpType1))=0,peek(tmpType1)) as Type1,
            Type2;
            Load
            Prod,
            Month,
            if(mod(recno,3)=2,Data) as tmpType1,
            if(mod(recno,3)=0,Data) as Type2
            Resident temp2
            Where mod(recno,3)<>1
            and len(trim(Prod))>0;

            DROP Table temp2;

            Table:
            NOCONCATENATE Load
            Prod,
            Month,
            Type1,
            Type2
            Resident temp3
            WHERE len(trim(Type2))>0;

            DROP Table temp3;


            Hope it helps you.

             



            • Help with Load Script -
              Anil Konduri

              Thanks for your reply... I have attached a sample .xls data file....

              And also I have to remove weeks columns and divide the Hours and Revenue by number of days .

              Thanks in advance...

               

                • Re. :Re: Help with Load Script -
                  Martin FAVIER

                  Here is your load script :

                   



                  temp:
                  CrossTable(Month, Data,4)
                  LOAD *
                  FROM
                  $(Path)\Sample.xls
                  (biff, embedded labels, table is [Report Data$]);


                  temp2:
                  Load
                  Reg,
                  Div,
                  Team,
                  Member,
                  if(isnum(mid(Month,2))=False() and len(trim(Month))>1,Month,peek(Month)) as Month,
                  Data,
                  recno() as recno
                  Resident temp
                  ;

                  DROP TABLE temp;

                  temp3:
                  Load Reg,
                  Div,
                  Team,
                  Member,
                  Month,
                  tmpHours,
                  if(len(trim(tmpHours))=0,peek(tmpHours)) as Hours,
                  Revenue;
                  Load
                  Reg,
                  Div,
                  Team,
                  Member,
                  Month,
                  if(mod(recno,3)=2,Data) as tmpHours,
                  if(mod(recno,3)=0,Data) as Revenue
                  Resident temp2
                  Where mod(recno,3)<>1
                  and len(trim(Reg))>0;

                  DROP Table temp2;

                  Table:
                  NOCONCATENATE Load
                  Reg,
                  Div,
                  Team,
                  Member,
                  Month,
                  Hours,
                  Revenue
                  Resident temp3
                  WHERE len(trim(Revenue))>0;

                  DROP Table temp3;


                  Hope it helps you