7 Replies Latest reply: Sep 14, 2010 12:50 PM by John Walker RSS

    Files loaded don't always contain the column - how to load

      Hi,

      Thanks for posting so much on how to use QV. First time I've needed to ask a question as I'm not sure how best to find the answer.

      I am loading several hundred daily transactional files. Each file has customer rows, and each column for that customer lists the count of a particular product being used.

      There has been a change in the format of these files in that they are now using additional columns.

      I changed my load script to pick up the new columns, but it now causes errors as these new columns do not appear in the previous x hundred files (Does not exist error).

      It's probably basic - but would you be able to advise on how best to load so that QV will ignore if the column doesn't exist?

      Hope that makes sense.

      Thanks

       

      John

        • Files loaded don't always contain the column - how to load
          Miguel Angel Baeyens de Arce

          Hello John and welcome to the Forums,

          Although probably not the most elegant way, my guess is concatenate both load statements, one for the files with old scheme then one new for those that have those fields.

          It depends very much on how are you loading your files, though, since if you are using a loop, you can check whether that field exist and then using one load statement or another.

          Regards.

            • Files loaded don't always contain the column - how to load

              Thanks

              I do concatenate the data in the load to ensure that I can use the column names for products.

               

               


              LOAD Date,
              Month(Date) as Month,
              OrgID,
              CustomerName,
              DomainName,
              Total_Billable
              FROM
              //[O:\xxx\QlikviewFiles\Daily*.*]
              [D:\QlikviewStorage\Finance\xxx_Daily\Daily*.*]
              (txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
              WHERE(Date > '01/04/2010');

              concatenate
              LOAD Date,
              Month(Date) as Month,
              OrgID,
              CustomerName,
              DomainName,
              ProductNameOne as Qty,
              'ProductNameOne' as Product
              FROM
              //[O:\xxx\QlikviewFiles\Daily*.*]
              [D:\QlikviewStorage\Finance\xxx_Daily\Daily*.*]
              (txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
              WHERE(Date > '01/04/2010');


              concatenate
              LOAD Date,
              Month(Date) as Month,
              OrgID,
              CustomerName,
              DomainName,
              ProductNameTwo as Qty,
              'ProductNameTwo' as Product
              FROM
              //[O:\xxx\QlikviewFiles\Daily*.*]
              [D:\QlikviewStorage\Finance\xxx_Daily\Daily*.*]
              (txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
              WHERE(Date > '01/04/2010');
              etc
              etc
              etc



              Unfortunately because of that I Have just one load script that tries to load all columns, regardless of whether they exist in the file.

              Could 'exists' be used in a load file - to ignore a load column if it doesn't exist?

              John

              "The more I use QV - the less I know"



            • Files loaded don't always contain the column - how to load

              Hi again,

              I think I'm having trouble explaining this.

              [View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/0777.Dummy.zip:550:0]

              I've attached a sample of the file that I'm trying to load - unsuccessfully.

              There's one of these files for each day. As new products are added to the product line, additional columns are added automatically to this file. Another thing to note is that, if a product isn't used on the day, the file will not contain the column.

              What I'm trying to do is load in all of these files so that I can click on 'Customer' and see all of their use (I can do that); but also load in the columns as products that I can also list and select on.

              I had tried loading in all the files in turn for each product, as shown in the script above, but I can't get it to handle the instances where a column does not exist.

              Hope this helps qualify my quandary. Would appreciate any suggestions on how to get around this.

              Thanks

              John

                • Files loaded don't always contain the column - how to load

                  crosstable might help you here. See page 476 of the reference manual for detailed explanation.
                  That will convert:

                  Date, cutomer, total, product1, product2, product3

                  1/1/2010, A, 10,1,2,3

                  1/1/2010,B,12,4,5,6

                  to:

                  date,customer,total,product,amount

                  1/1/2010,A,10,product1,1
                  1/1/2010,A,10,product2,2
                  1/1/2010,A,10,product3,3

                  1/1/2010,B,12,product1,4
                  1/1/2010,B,12,product2,5
                  1/1/2010,B,12,product3,6

                  You might have to process each file separately, not sure what will happen if there are different columns in each file and you try to read with one load. Also depending on what "total billable" means, you may want to separate that into a different header table so the numbers don't get double counted.

                  Steve

                   

                   


                   

                   

                   

                  • Files loaded don't always contain the column - how to load
                    Miguel Angel Baeyens de Arce

                    Hello John,

                    Going further on Steve's suggestion, you may use the following code to load your dummy text document:

                     

                    CROSSTABLE (Products, Amount, 3)LOAD *FROM[C:\Dummy.txt](txt, codepage is 1252, embedded labels, delimiter is '\t', msq);


                    Is this useful? Since you are using "*" you will load all fields, regardless the number of actual fields for each record, and having that Date, CustomerName and Total_Billable will always be there the code above will work.

                    Regards

                     

                  • Files loaded don't always contain the column - how to load

                    Steve, Miguel,

                    Thanks. Crosstable appears to have cracked it. Not something I was aware of before.

                    John