Discussion Board for collaboration related to QlikView App Development.
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.
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
with id field make an outer join with a single field table with 3 rows : city,country,region
to have this :
then join this table with your generic load with if(isnull(value),'N/A', value ) as value
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.
maybe I was not obvious,
I've well understood id being joined,
here is an example :
table test :
load distinct id resident test;
load distinct group resident test;
outer join (tempid) load group resident tempgroup;
you get :
left join (tempid) load id,group ,score resident test;
you get new column with your value known
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
But I need it out of generic load. How to achieve with this ?
Generic load table create separate tables for each of the 'key'
ID, Key, attribute;
and we can't use Noconcatenate for this.
Can you please share a working sample.
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)