Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

13 Replies
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
Partner
Partner

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

Not applicable
Author

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
Not applicable
Author

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

SET var= $(ScriptErrorDetails);

Not applicable
Author

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
Not applicable
Author

another easier idea:

Set NullAsValue *;

Set NullValue = ' ';

Then Load everything with explicit concatenate

LOAD * ...;

concatenate

LOAD * ...;

Not applicable
Author

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