Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Not applicable
Author

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


View solution in original post

7 Replies
Miguel_Angel_Baeyens

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.

Not applicable
Author

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
//

(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
//

(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
//

(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"



Not applicable
Author

You seem to be reading the same files more than once. If you know when the file structire chnaged can you chnage the FROM filename pattern to match the appropriate set of files.

Steve

Not applicable
Author

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

Not applicable
Author

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


Miguel_Angel_Baeyens

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(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

Not applicable
Author

Steve, Miguel,

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

John