If field is not found, load as blank, else load as it is

[#1]

I have a txt file which contains fields A,B ,C. However, some of the txt files only contains fields B and C. I am using a wild card * load to load all txt files whether they have field A or not. If they have field A, I want them to load, else load as BLANK.

How do I write something like

IF (A, A, ' ')  AS COLUMN_A

Thank you

[Peter_Cammaert]

If the text files are intended to be merged into a single table, you can let QlikView do an (auto-)concatenate using LOAD *. The missing fields won't cause any problems because QlikView will make sure that fields are added to each row to make it fit into the existing table. In addition to that, if a new column is detected that wasn't there previously, the original table will be expanded.

In all cases, missing fields will contain NULL values. Those are easy to detect, and will be ignored in most QlikView chart aggregation functions. If you really want to store empty strings in those missing fields, load your initial table into a second Resident table while replacing NULL values with whathever suitable replacement value you have. For Numerical values, you can use the Alt() function.

Best,

Peter

[Peter_Cammaert]

A recent discussion on a related topic can be found here: how to identify if a field is present in the load statement?

It contains suggestions for filling up missing fields.

[MindaugasBacius]

If you have two tables like:

A B C

1 2 3

2 3 4

B C

3 4

5 6

And just try simple to load them:

Load *

From *.txt

The QV will simply concatenate them like:

A B C

1 2 3

2 3 4

   3 4

    5 6

[#5]

Hi what if I want to load A as Apple, B as Banana and C as Carrot? How would I go about doing that while checking if the fields exists as well?

[Peter_Cammaert]

Transfer the merged table into a second resident table, thereby explicitly listing each column. The ones that need filling can be specified along this pattern:

:

IF (IsNull(A), 'Apple', A) AS A,

:

or even better (also replaces empty strings):

:

IF (len(trim(A)) = 0, 'Apple', A) AS A,

:

[Anonymous]

if you use this technique, then you can also read out which fields are missing:

SET var= $(ScriptErrorDetails);

[#8]

Hi I read your solution in the other thread and I think its good. However, I have a problem. In actual fact, I have like 10 files (or more). Each file can contain about 10+ fields, lets say A,B,C,D,E,F,G,H,I,J. However, they can also have lesser like A, B, C, D, E, F, G or A, B, C, D, E, F,G ,H, I.

If I do "if $(ScriptError)=11, LOAD ...." to load specific fields, the number of possibilities would be too great wouldn't it? How do I get around this problem and LOAD as blank ONLY IF the field is not found?

[Anonymous]

another easier idea:

Set NullAsValue *;

Set NullValue = ' ';

Then Load everything with explicit concatenate

LOAD * ...;

concatenate

LOAD * ...;

[#10]

Is there a way that I can load using wildcards instead? This is because every month, I may get different txt files and Loading everything with explicit concatenate is not extensible to all these new files every month