How to Load same structure files i Qliksense?

[sivaboggarapu20]

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:

?

---

[OmarBenSalem · Accepted solution]

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);

[sivaboggarapu20]

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]

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

[sivaboggarapu20]

Thanks Praveen

[sivaboggarapu20]

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]

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:

Imported source:

Please change your lib connecion when copying the script

[sivaboggarapu20]

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