topic Re: Create a field in QlikView

Create a field

qvqfqlik — Tue, 12 Jun 2018 19:03:18 GMT

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

Re: Create a field

ogautier62 — Wed, 13 Jun 2018 18:35:36 GMT

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

Re: Create a field

qvqfqlik — Wed, 13 Jun 2018 20:57:56 GMT

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.

Re: Create a field

ogautier62 — Thu, 14 Jun 2018 07:39:09 GMT

Hi,

maybe I was not obvious,

I've well understood id being joined,

here is an example :

table :

table test :

group	id	score
A	1	5
B	1	3
C	1	1
A	2	2
B	2	3
B	3	4
A	4	4
C	4	2
A	5	2
B	5	2
C	5	1

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

Re: Create a field

qvqfqlik — Tue, 19 Jun 2018 18:53:38 GMT

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.

Re: Create a field

qvqfqlik — Tue, 19 Jun 2018 19:44:24 GMT

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)