7 Replies Latest reply: Jun 14, 2018 9:06 AM by Siva Sankar Boggarapu RSS

    How to Load same structure files i  Qliksense?

    Siva Sankar Boggarapu

      Hi Team,

       

      I have the below Attached files. I am Loading like below. But its giving error. Both Files are having same no of fileds.

       

      If I am doing with Concatination . It working fine. But Loading with * not working.

       

      Please let me know what is the issue?

       

      Billing:

      LOAD

          InvoiceID,

          PayerAccountId,

          LinkedAccountId,

          RecordType,

          RecordId,

          BillingPeriodStartDate,

          BillingPeriodEndDate,

          InvoiceDate,

          PayerAccountName,

          LinkedAccountName,

          TaxationAddress,

          PayerPONumber,

          ProductCode,

          ProductName,

          SellerOfRecord,

          UsageType,

          Operation,

          AvailabilityZone,

          RateId,

          ItemDescription,

          UsageStartDate,

          UsageEndDate,

          UsageQuantity,

          BlendedRate,

          CurrencyCode,

          CostBeforeTax,

          Credits,

          TaxAmount,

          TaxType,

          TotalCost,

          "aws:autoscaling:groupName",

          "aws:cloudformation:logical-id",

          "aws:cloudformation:stack-id",

          "aws:cloudformation:stack-name",

          "aws:createdBy",

          "user:CAL:ACCOUNT",

          "user:CAL:CALID",

          "user:CAL:INSTANCEID",

          "user:CAL:SOLINSTID",

          "user:CAL:SOLUTION",

          "user:CAL:TENANTID",

          "user:CAL:TENANTNAME",

          "user:CAL:USERID",

          "user:Customer",

          "user:Demo",

          "user:Function",

          "user:InstanceType",

          "user:KubernetesCluster",

          "user:Name",

          "user:Product",

          "user:ProductName",

          "user:Project",

          "user:Project1",

          "user:Purpose",

          "user:Qlik",

          "user:Role",

          "user:SID",

          "user:k8s.io/etcd/events",

          "user:k8s.io/etcd/main",

          "user:k8s.io/role/master",

          "user:kubernetes.io/cluster/kops.boltconnections.today",

          "user:kubernetes.io/created-for/pv/name",

          "user:kubernetes.io/created-for/pvc/name",

          "user:kubernetes.io/created-for/pvc/namespace",

          "user:workload-type"

      FROM [lib://QVD/726318260466-aws-cost-allocation*.csv]

      (txt, codepage is 28591, embedded labels, delimiter is ',', msq, header is 1 lines);

       

       

      Billing << 726318260466-aws-cost-allocation-2018-04

       

      Lines fetched: 2,153

       

       

      ---

       

       

      The following error occurred:

       

       

      Field 'user:Demo' not found

       

       

      ---

       

       

      The error occurred here:

       

       

      ?

       

       

      ---

       

        • Re: How to Load same structure files i  Qliksense?
          omar bensalem

          works fine for me :

          LOAD

              InvoiceID,

              PayerAccountId,

              LinkedAccountId,

              RecordType,

              RecordId,

              BillingPeriodStartDate,

              BillingPeriodEndDate,

              InvoiceDate,

              PayerAccountName,

              LinkedAccountName,

              TaxationAddress,

              PayerPONumber,

              ProductCode,

              ProductName,

              SellerOfRecord,

              UsageType,

              Operation,

              AvailabilityZone,

              RateId,

              ItemDescription,

              UsageStartDate,

              UsageEndDate,

              UsageQuantity,

              BlendedRate,

              CurrencyCode,

              CostBeforeTax,

              Credits,

              TaxAmount,

              TaxType,

              TotalCost,

              "aws:autoscaling:groupName",

              "aws:cloudformation:logical-id",

              "aws:cloudformation:stack-id",

              "aws:cloudformation:stack-name",

              "aws:createdBy",

              "user:CAL:ACCOUNT",

              "user:CAL:CALID",

              "user:CAL:INSTANCEID",

              "user:CAL:SOLINSTID",

              "user:CAL:SOLUTION",

              "user:CAL:TENANTID",

              "user:CAL:TENANTNAME",

              "user:CAL:USERID",

              "user:Customer",

              "user:Function",

              "user:InstanceType",

              "user:KubernetesCluster",

              "user:Name",

              "user:Product",

              "user:ProductName",

              "user:Project",

              "user:Project1",

              "user:Purpose",

              "user:Qlik",

              "user:Role",

              "user:SID",

              "user:k8s.io/etcd/events",

              "user:k8s.io/etcd/main",

              "user:k8s.io/role/master",

              "user:kubernetes.io/cluster/kops.boltconnections.today",

              "user:kubernetes.io/created-for/pv/name",

              "user:kubernetes.io/created-for/pvc/name",

              "user:kubernetes.io/created-for/pvc/namespace",

              "user:workload-type"

          FROM [lib://csv/726318260466-aws-cost-allocation*.csv]

          (txt, codepage is 28591, embedded labels, delimiter is ',', msq, header is 1 lines);

           

          Capture.PNG

            • Re: How to Load same structure files i  Qliksense?
              Siva Sankar Boggarapu

              Hi Omar,

               

              Sorry I forgot mansion one thing here

               

              Thanks for Quick Reply. Its working fine with me If I have only above two files.

               

              But If I am giving

               

              726318260466-aws-cost-allocation*. The Script is taking below files as well for Load. But I don't want Load below files


              How to avoid the loading files without below ?

               

              726318260466-aws-cost-allocation-AWSMarketplace-2018-04

              726318260466-aws-cost-allocation-AWSMarketplace-2018-05

               

              Thanks,

              Siva

                • Re: How to Load same structure files i  Qliksense?
                  omar bensalem

                  If I fully understand; you have a folder where u have, let's say: 150 csv files;

                  you want to import data from 148 files but don't import any data from the 2 previously mentionned csv?

                   

                  Is it so?

                   

                  If that's the case, please try as follow:

                   

                  Data0 :

                  load 0 as field AutoGenerate(0);

                   

                  For Each vPath in FileList('lib://com/*.csv')

                  Concatenate

                  load '$(vPath)' as path,

                  FileName('$(vPath)') as filename

                  FROM [$(vPath)]

                  (txt, codepage is 28591, embedded labels, delimiter is ',', msq, header is 1 lines);

                   

                  Next vPath

                   

                  Drop Field field From Data0;

                  Data:

                  NoConcatenate

                  load Distinct filename Resident Data0 where filename <> '726318260466-aws-cost-allocation-2018-04.csv' and filename <> '726318260466-aws-cost-allocation-2018-05.csv' ;

                  drop table Data0;

                   

                   

                  FOR i = 1 to FieldValueCount('filename')

                  LET vFile$(i) = FieldValue('filename', $(i));

                   

                  Final:

                  load 0 as field AutoGenerate(0);

                  Concatenate(Final)

                  LOAD

                     *, '$(vFile$(i))' as source

                  FROM [lib://com/$(vFile$(i))]

                  (txt, codepage is 28591, embedded labels, delimiter is ',', msq, header is 1 lines);

                   

                  drop Field field from Final;

                  next i

                  drop Table Data;

                   

                  Result:

                  Original souce:

                  Capture.PNG

                  Imported source:

                  Capture.PNG

                   

                   

                  Please change your lib connecion when copying the script

                    • Re: How to Load same structure files i  Qliksense?
                      Siva Sankar Boggarapu

                      Hi Omar,

                       

                      Thanks for helping me in this case. I appreciate your help.

                       

                      But I have question now, with above Script I loaded 148 files excluding Loading 726318260466-aws-cost-allocation-2018-04.csv and 726318260466-aws-cost-allocation-2018-05.csv.


                      But in Future If the Count gets increase lets say 500 files in Folder.


                      From which files on words Its start Loading. The Script has to load only new files instead of Loading from beginning and store into QVD

                       

                      Thanks,

                      Siva

                  • Re: How to Load same structure files i  Qliksense?
                    Siva Sankar Boggarapu

                    Hi Omar,

                     

                    This is about yesterday issue.

                     

                    Create Nightly Load Script

                     

                     

                    I have created Load script. Please let me know is that correct?

                     

                    Thanks,

                    Siva

                  • Re: How to Load same structure files i  Qliksense?
                    praveen kumar

                    Hi Siva,

                     

                    I Had checked the Files below

                    April File

                    May File

                     

                     

                    In April File   'user:Demo'  Filed is available


                    where as in May File


                    there is no Field   'user:Demo'

                    So you are Getting error..


                    so now you find the error , do necessary changes and load , it will load with  out error.


                    This might be Helpful..


                    Thanks

                    PM