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: 
qvqfqlik
Creator
Creator

Create a field

There is a scenario where I need to create a field based on if it exists or not and this is out of generic load.

eg:

Generic load

ID, key, value resident table1;

key has fieldnames like  city, country,region

I need to create a field "country" for the records that doesn't have "country" field coming out of this load.

eg: sample data that I have is below

id   key        value

1   city          city1

1   country      country1

1     region     region1

2      city          city2

2     region     region2

now I need to create record for id=2 as it does not have fieldname "country" in the sample data above

id      key     value

2     country    N/A

Please help with how to achieve this result

5 Replies
ogautier62
Specialist II
Specialist II

HI,

an idea:

with id field make an outer join with a single field table with 3 rows : city,country,region

to have this :

1 city

1 country

1 region

2 city

etc

then join this table with your generic load with if(isnull(value),'N/A', value ) as value

regards

qvqfqlik
Creator
Creator
Author

Here we should not  be checking the values as Isnull. Need to look for 'field' (column header) available or no

Id's are being joined to a table before generic load.

ogautier62
Specialist II
Specialist II

Hi,

maybe I was not obvious,

I've well understood id being joined,

here is an example :

table :

   table test :

groupidscore
A15
B13
C11
A22
B23
B34
A44
C42
A52
B52
C51

tempid:

load distinct id resident test;

tempgroup:

load distinct group resident test;

outer join (tempid) load group resident tempgroup;

you get :

1 A

1 B

1 C

2 A

2 B

2 C

etc

left join (tempid) load  id,group ,score resident test;

you get new column with your value known

temp:

noconcatenate load id,group, if(isnull(score),'N/A',score) as score resident tempid;

drop table test;

drop table tempid;

you get now N/A if row A,B or C not exist in generic table

regards

qvqfqlik
Creator
Creator
Author

Thanks Olivier!

But I need it out of generic load. How to achieve with this ?

Generic load table create separate tables for each of the 'key'

Generic Load

ID, Key, attribute;

and we can't use Noconcatenate for this.

Can you please share a working sample.

qvqfqlik
Creator
Creator
Author

Thanks Olivier!

how can I get just one key...not all keys. like in my initial post..

I want to insert only the 'country' key for the records (if they don't already have it)