Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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

Highlighted
Creator
Creator

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.

Highlighted
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

Highlighted
Creator
Creator

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.

Highlighted
Creator
Creator

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)