Creat new field combining other fields, conditional on combined value previously loaded in inline table

[mazacini]

Hi

In my load script, I want to create a new field combining a number of other fields, but depending on the value of the combined fields.

If the value of the combined fields exists in a previously loaded inline table, the new field will have a certain value. If it does not exist, it will have a different value.

So in my inline table, I have a field name X, which contains values A&B&C.

In my script, I combine Fields A, B and C. If A&B&C exist in the inline table, value is A&B&C. If not value is A&Other.

I hope this explains my problem,

Tks

Joe

[#2]

Hi,

Maybe you can use the exists() function, this function checks if a value exists in a field.

Please upload an example QVW that explains your problem so it can be easier to help you

Regards!

[mazacini]

Hi Gabriela

My qvw file contains sensitive customer data, so I will not be able to post it.

There are 3 fields involved. Type,PAREA,DAREA which I want to combine into a new field ROUTE.

In my inline table, I have a ROUTE field containing some possible combinations of the 3 fields eg B_D1_D2.

In my script, if TYPE&'_'&PAREA&'_'&DAREA is present in the ROUTE field in my inline table, then I want to load that value as ROUTE. If not I want to load another value.

So, for instance, if TYPE&'_'&PAREA&'_'&DAREA = B_D1_D2, then load B_D1_D2 as ROUTE, if not, load B_Other as ROUTE

Hope this helps

[#4]

try this:

Data:

Load TYPE&'_'&PAREA&'_'&DAREA as new_route

from .... where exists (ROUTE, TYPE&'_'&PAREA&'_'&DAREA);

concatenate(Data)

Load TYPE&'_'&OTHER as new_route

from .... where not exists (ROUTE, TYPE&'_'&PAREA&'_'&DAREA);

Regards!

[mazacini]

Hi Gabriela

Thank you for your speedy response.

I have no SQL expertise, so could you explain your answer?

I need all records to load (I should have mentioned, there are a number of other fields involved).

I see you suggest creating a new-route field. I can understand that.

But does your solution mean I split the load into 2 parts.The first load will load only those records where the ROUTE exists. Then a second load where the ROUTE does not exist?

So

LOAD Field1,

Field2,

Field3 etc,

'TYPE'&'_'&PAREA&'_'&DAREA as new_route etc

then

second LOAD

Thanks

Joe

[#6 · Accepted solution]

Hi Joe,

In the first part you load only the rows that have a ROUTE, it doesn't matter if you have more fields it still works, in the second part you load the rest of the rows, this will generate two tables, with the same fields including new_route; if two tables have the same name and number of fields qlikview will concatenate them automaticaly, so the result is one table with the rows of the first table + the rows of the second table, you can use the function concatenate(table_name) to do the same;you can find more information and examples about the exists() and concatenate() functions in the QlikView help

Hope this explains better

Regards!

[mazacini]

Hi Gabriela

Thank you for your solution and for taking the time to explain!

Joe

[#8]

HI All,

I have a doubt. I am having a filed Quality Description. I need to create a new field for all the records having qualtiy desc. the value of this field will be 'Yes' if the quality Desc ends with 'FSR' and No if its not 'FSR. later I will calculate the percentage of yes over no to show in input box.

PLs guide

[mazacini]

Hi

Try this in your script

if(right([Quality Description],3)='FSR','y','n') as NewField,

Rgds

Joe