Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
sivaboggarapu20
New Contributor III

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:CALSmiley FrustratedOLINSTID",

    "user:CALSmiley FrustratedOLUTION",

    "user:CAL:TENANTID",

    "user:CAL:TENANTNAME",

    "user:CAL:USERID",

    "user:Customer",

    "userSmiley Very Happyemo",

    "user:Function",

    "user:InstanceType",

    "user:KubernetesCluster",

    "user:Name",

    "userSmiley Tongueroduct",

    "userSmiley TongueroductName",

    "userSmiley Tongueroject",

    "userSmiley Tongueroject1",

    "userSmiley Tongueurpose",

    "user:Qlik",

    "user:Role",

    "userSmiley FrustratedID",

    "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 'userSmiley Very Happyemo' not found

---

The error occurred here:

?

---

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

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:CALSmiley FrustratedOLINSTID",

    "user:CALSmiley FrustratedOLUTION",

    "user:CAL:TENANTID",

    "user:CAL:TENANTNAME",

    "user:CAL:USERID",

    "user:Customer",

    "user:Function",

    "user:InstanceType",

    "user:KubernetesCluster",

    "user:Name",

    "userSmiley Tongueroduct",

    "userSmiley TongueroductName",

    "userSmiley Tongueroject",

    "userSmiley Tongueroject1",

    "userSmiley Tongueurpose",

    "user:Qlik",

    "user:Role",

    "userSmiley FrustratedID",

    "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
OmarBenSalem
Esteemed Contributor

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:CALSmiley FrustratedOLINSTID",

    "user:CALSmiley FrustratedOLUTION",

    "user:CAL:TENANTID",

    "user:CAL:TENANTNAME",

    "user:CAL:USERID",

    "user:Customer",

    "user:Function",

    "user:InstanceType",

    "user:KubernetesCluster",

    "user:Name",

    "userSmiley Tongueroduct",

    "userSmiley TongueroductName",

    "userSmiley Tongueroject",

    "userSmiley Tongueroject1",

    "userSmiley Tongueurpose",

    "user:Qlik",

    "user:Role",

    "userSmiley FrustratedID",

    "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

sivaboggarapu20
New Contributor III

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

praveenkumar_ma
Contributor

Re: How to Load same structure files i Qliksense?

Hi Siva,

I Had checked the Files below

April File

May File

In April File   'userSmiley Very Happyemo'  Filed is available


where as in May File


there is no Field   'userSmiley Very Happyemo'

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

sivaboggarapu20
New Contributor III

Re: How to Load same structure files i Qliksense?

Thanks Praveen

sivaboggarapu20
New Contributor III

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

OmarBenSalem
Esteemed Contributor

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

sivaboggarapu20
New Contributor III

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

Community Browser