topic Re: maxstring to return data if contains Y in QlikView

maxstring to return data if contains Y

dmxmikey — Wed, 25 Nov 2020 16:16:04 GMT

Im trying to return word Egg on my table on Field that says Y and Blank if its N of blank.

Database contains Y,N and blank

Re: maxstring to return data if contains Y

Anil_Babu_Samineni — Mon, 12 Jun 2017 16:39:58 GMT

This is the case, I don't think whether you need MaxString(). Why not simply IF condition without MaxString() function

Re: maxstring to return data if contains Y

sunny_talwar — Mon, 12 Jun 2017 16:41:40 GMT

Where exactly are you using Y, N? on which field I mean?

Re: maxstring to return data if contains Y

dmxmikey — Mon, 12 Jun 2017 16:46:22 GMT

The Y,N is the alg-stat

Re: maxstring to return data if contains Y

dmxmikey — Mon, 12 Jun 2017 16:48:28 GMT

sorry how will the if command look like?

Re: maxstring to return data if contains Y

sunny_talwar — Mon, 12 Jun 2017 16:48:53 GMT

But you are testing for values such as GLUT, EGG against alg-stat? Does it contain both?

Re: maxstring to return data if contains Y

Anil_Babu_Samineni — Mon, 12 Jun 2017 16:57:14 GMT

My intention is this?

Load Distinct [p-code],

[alg-code],

If(Match([alg-code],'GLUT','EGG','CRUS'), 'Y', 'N') as Flag

Re: maxstring to return data if contains Y

dmxmikey — Mon, 12 Jun 2017 17:05:12 GMT

	Alg-code
Cake	Y
coke	N

the data looks like this on Database, where Y,N are Alg-stat,

so what i need to achieve is where Y i need it to say Egg and N to be blank. This is just for the Egg Alg-code

Re: maxstring to return data if contains Y

dmxmikey — Mon, 12 Jun 2017 17:05:28 GMT

Alg-code
Cake	Y
coke	N

the data looks like this on Database, where Y,N are Alg-stat,

so what i need to achieve is where Y i need it to say Egg and N to be blank. This is just for the Egg Alg-code

maxstring to return data if contains Y

Re: maxstring to return data if contains Y

Anil_Babu_Samineni — Mon, 12 Jun 2017 17:13:58 GMT

May be this?

Load Distinct [p-code], If([Alg-code]='Y', 'Egg', 'Blank') as Egg;

Re: maxstring to return data if contains Y

sunny_talwar — Mon, 12 Jun 2017 17:15:32 GMT

May be this

If([alg-code] = 'EGG' and [alg-stat] = 'Y', 'Egg', Null()) as Egg

Re: maxstring to return data if contains Y

dmxmikey — Mon, 12 Jun 2017 17:23:11 GMT

Allergy_tmp:

Load Distinct [p-code],

MaxString(If([alg-code]='GLUT',[alg-stat])) as Gluten,

MaxString(If([alg-code]='EGG',[alg-stat])) as Egg,

MaxString(If([alg-code]='CRUS',[alg-stat])) as Crustaceans,

MaxString(If([alg-code]='CLRY',[alg-stat])) as Celery,

MaxString(If([alg-code]='SOY',[alg-stat])) as Soyabeans,

MaxString(If([alg-code]='SESM',[alg-stat])) as Sesame,

MaxString(If([alg-code]='SO2',[alg-stat])) as Sulphur_Dioxide,

MaxString(If([alg-code]='LUP',[alg-stat])) as Lupin,

MaxString(If([alg-code]='MILK',[alg-stat])) as Milk,

MaxString(If([alg-code]='PNUT',[alg-stat])) as Peanuts,

MaxString(If([alg-code]='NUT',[alg-stat])) as Treenuts,

MaxString(If([alg-code]='MSTD',[alg-stat])) as Mustard,

MaxString(If([alg-code]='FISH',[alg-stat])) as Fish,

MaxString(If([alg-code]='MLSC',[alg-stat])) as Molluscs

Resident Products

Group By [p-code];

Drop Fields [alg-code],[alg-stat] from Products;

tmp:

NoConcatenate

Load Distinct *

resident Products;

Drop Table Products;

RENAME table tmp to Products;

Left Join(Products)

Load [p-code],

Gluten,

Egg,

Crustaceans,

Celery,

Soyabeans,

Sesame,

Sulphur_Dioxide,

Lupin,

Milk,

Peanuts,

Treenuts,

Mustard,

Fish,

Molluscs

Resident Allergy_tmp;

Drop Table Allergy_tmp;

Above is the whole script and works but returning Y or N under each alg-code

what im trying to archieve is replace Y with the name each alg code.

Hope makes sense