Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
YPOC
Contributor
Contributor

Add a column to an SQL Selected Table

Hello,

I'm loading a CSV file from a REST connection, now I want to add a column to it with a key so I can link the entries with other tables.

I'm loading the CSV like this:
for i = 1 to 5
let filename = i & '.csv';
RestConnectorMasterTable:
SQL SELECT
"MyCol1",
"MyCol2",
...
FROM CSV (header on, delimiter ",", quote """") "CSV_source"
WITH CONNECTION(Url "$(filename)");
NEXT i;

So I will get a big table with the entries of all CSVs right beneath each other. However I don't have a way to distinguish from which file they came. That's why I would like to add an extra column 'file' that stores the file name where each entry got loaded from.

If I understand correctly I can't use any calculated fields with an SQL Select statement directly. I thought of using
LOAD * INLINE [
// file
// '$(filename)'
// ](delimiter is ',');
right after the statement above, but this will straight up deliver no results. I think using Join or Keep are no options either, because I don't have a key to match yet.

What would be the best way to accomplish this?

Labels (1)
1 Solution

Accepted Solutions
PrashantSangle

use preceding load to achieve that

 

like 

 

for i = 1 to 5
let filename = i & '.csv';
RestConnectorMasterTable:

Load *, filename() as filename;

SQL SELECT
"MyCol1",
"MyCol2",
...
FROM CSV (header on, delimiter ",", quote """") "CSV_source"
WITH CONNECTION(Url "$(filename)");
NEXT i;

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

1 Reply
PrashantSangle

use preceding load to achieve that

 

like 

 

for i = 1 to 5
let filename = i & '.csv';
RestConnectorMasterTable:

Load *, filename() as filename;

SQL SELECT
"MyCol1",
"MyCol2",
...
FROM CSV (header on, delimiter ",", quote """") "CSV_source"
WITH CONNECTION(Url "$(filename)");
NEXT i;

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂