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: 
microwin88x
Creator III
Creator III

Concatenate Tables (CSV) with Different Number of Columns

Hello!

I have a folder named SELECTIONS which includes files like the following:

  • Save_15_03_2013_17_03_35.csv
  • Save_18_04_2013_10_15_53.csv
  • Save_22_05_2014_12_27_48.csv

Where the file format is: Save_DD_MM_YYYY_HH_MM_SS.csv

I use the load function to get all the files in my application using: from Save_*.csv.

The issue comes here:

*** The files older than the date 15/05/2014 (DD/MM/YYYY) bring up 3 columns that I should load:

  • ACCOUNT_ID (the accounts in my model)
  • ZONE (the zone for a corresponding account)
  • SAVE_DATE (the same date as the one that appears in the filename)

*** The files greater or equal that the date 15/05/2014 (DD/MM/YYYY) bring up 6 columns that I should load:

  • ACCOUNT_ID (the accounts in my model)
  • ZONE (the zone for a corresponding account)
  • SAVE_DATE (the same date as the one that appears in the filename)
  • Q_OBSERVATIONS (the number of observations for that account)
  • Q_INSPECTIONS (the number of inspections for that account)
  • Q_COMPLAINTS (the number of complaints for that account)

My question is how to do this, because first of all I tried loading all the 6 fields for all the files (using: from Save_*.csv), but I get an error that I believe it is because of the files that only have 3 fields where doesn't exist the other 3 fields...

How could I do to load all the files and create a single table with all the data and the 6 fields?

Thank you!!!

1 Solution

Accepted Solutions
Not applicable

not sure what error are you referring to but maybe do something like this:

//Create a blank table with the columns you need to avoid synthetic keys

[HOLD]:

load * inline

[ACCOUNT_ID,ZONE,SAVE_DATE,Q_OB,Q_IN,Q_CO

];

//force concatenation

Concatenate

//load the data

[DATA]:

LOAD *

FROM

(ooxml, embedded labels, table is Sheet1);

You can later drop columns that you don't need or do a resident load with the required columns.

View solution in original post

3 Replies
Not applicable

not sure what error are you referring to but maybe do something like this:

//Create a blank table with the columns you need to avoid synthetic keys

[HOLD]:

load * inline

[ACCOUNT_ID,ZONE,SAVE_DATE,Q_OB,Q_IN,Q_CO

];

//force concatenation

Concatenate

//load the data

[DATA]:

LOAD *

FROM

(ooxml, embedded labels, table is Sheet1);

You can later drop columns that you don't need or do a resident load with the required columns.

Not applicable

Hi Micro,

Try this

    Load  *,

from Save_*;

It takes all fields from all files.

Hopes it will helpful.

Thanks,

Ashok.

Not applicable

HI Ashok,

Your solution will create two tables (plus synth key) but the op was asking for a single table.

Cheers,

Paul