hardcoded data in data loader

[Ethel]

I need to add column to my table that contains A,B,C rows .

I know I can concatenate my table 3 times 

load 

...

'A' as   specialty

from...

concatenate

load 

...

'B' as   specialty

from...

concatenate

load 

...

'C' as   specialty

from ..

But is there an easier way to do it?

Thanks a lot in advance!

[anthonyj]

Hi @Ethel ,

You can perform a loop calling out your column names like this:

data:
load * Inline [
A,B,C
1,2,3
4,5,6
7,8,9
];

//Loop through each of the column headers in a comma separated list. (Must be in single quotes)

for each i in 'A', 'B', 'C';

Output:
Load
[$(i)] as Specialty
Resident data;
next i;

drop table data;

Qlik auto concatenates like for like data so there's no need to explicitly concatenate.

Thanks

Anthony

[Ethel]

Thank you very much!

Could you please give me more details.

Should My code look like this? I think I'm missing something....

data:

Load 
FiscalYear
from
[Lib://.../1.qvd] (qvd)
where FiscalYear ='2022';

load * Inline [
A,B,C
];

for each i in 'A', 'B', 'C';

Output:
Load
[$(i)] as Specialty
FiscalYear;
next i;

drop table data;

[anthonyj]

No worries,

Your FiscalYear load is only loading that column. If that is the table that's holding your specialty columns then you'll need to read them all in first and then loop through.

For example:

data:
Load
Specialty1,
Specialty2,
Specialty3
from [Lib://.../1.qvd] (qvd)
where FiscalYear ='2022';

for each i in 'Specialty1', 'Specialty2', 'Specialty3';

Output:
Load
[$(i)] as Specialty
Resident FiscalYear;

next i;

drop table data;

I hope this makes sense.

Thanks

Anthony

[Ethel]

No...I'm sorry, it's not working for me...

here is my code

data:
load * Inline [
A,B,C
];
Load
FiscalYear
,P1
,P2
from [Lib://.../1.qvd] (qvd)
where FiscalYear ='2022';
for each i in FiscalYear,P1,P2, ;

Output:
Load
[$(i)] as Rank,
Resident
FiscalYear,

P1,

P2

next i;

drop table data;

I think I'm missing something...How should it look all together?

[anthonyj]

I may have misunderstood what you're trying to accomplish. The code I gave you will create a list of specialties from your P1 and P2 columns. Can I confirm what the output table to look like? What are the columns and what do they consist of?

Thanks

Anthony

[Ethel]

I have a table with column three column: FiscalYear, P1, P2(come from qvd file).I need to add 4th column, that will have 3 values(3 rows) A,B,C.  

That's the code that I'm using now to add specialty column

load 

FiscalYear,

P1,

P2,

'A' as   specialty

from...

concatenate

load 

FiscalYear,

P1,

P2,

'B' as   specialty

from...

concatenate

load 

FiscalYear,

P1,

P2,

'C' as   specialty

from ..

Is it possible to simplify my code?

Thanks a lot in advance!

[anthonyj]

Okay, so you want the Fiscal Year which will be 2022 by the looks of your "where" statement. This will then be duplicated with every iteration of the loop and read in of A, B and C.

So this will read in your table 3 times. Each time it reads the same rows and columns from FiscalYear, P1 and P2 but it will concatenate the values that are in 'A', 'B' and 'C' into one column called Specialty essentially making your table long instead of wide.

for each i in 'A', 'B', 'C';

Output:
Load

FiscalYear
,P1
,P2

[$(i)] as Specialty
from [Lib://.../1.qvd] (qvd)
where FiscalYear ='2022';

next i;

Thanks

Anthony

[Ethel]

I'm getting error that field A is not found. This is my code:

data:
load * Inline [
A,B,C
];

for each i in 'A', 'B', 'C';

Output:
Load

FiscalYear,

P1,

P2,

,[$(i)] as Specialty
from [Lib://.../1.qvd] (qvd)
where FiscalYear ='2022';

next i;

[anthonyj · Accepted solution]

Hi,

The "inline" statement is used for loading manually written data into a table.

For example:

data:
load * Inline [
A,B,C
1,2,3
4,5,6
7,8,9
];

Makes columns A, B and C. The values in column A are: 1, 4 and 7.

You're getting an error because your inline statement only has column names but no data. I used it in my loop to demonstrate how the input of 3 columns can be made into one column by looping through the values and concatenating them under one column name. The inline statement is a stand in for your data that includes the columns you want to call "Specialty". 

What are the columns called that you are concatenating to call "Specialty" in your data? Are they in the same dataset that holds FiscalYear, P1 and P2?

Thanks

Anthony