Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

How to Load same structure files i Qliksense?

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:

?

---

1 Solution

Accepted Solutions
Partner
Partner

Re: How to Load same structure files i Qliksense?

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

7 Replies
Partner
Partner

Re: How to Load same structure files i Qliksense?

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

Partner
Partner

Re: How to Load same structure files i Qliksense?

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

Partner
Partner

Re: How to Load same structure files i Qliksense?

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

Partner
Partner

Re: How to Load same structure files i Qliksense?

Thanks Praveen

Partner
Partner

Re: How to Load same structure files i Qliksense?

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

Partner
Partner

Re: How to Load same structure files i Qliksense?

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

Partner
Partner

Re: How to Load same structure files i Qliksense?

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