topic Re: requirement in QlikView

requirement

Anonymous — Tue, 05 Dec 2017 07:54:38 GMT

i have a requirement :

I have below data:

field1	field2
123	123:None
456	456:None
345	345:None

now the values in colfield2 should be replaced with none ,

how to do that

Re: requirement

Anonymous — Tue, 05 Dec 2017 08:01:52 GMT

Can you try

Purgechar(field2,'0123456789:') as newField

Re: requirement

antoniotiman — Tue, 05 Dec 2017 08:03:48 GMT

SubField(field2,':',-1)

Re: requirement

Anonymous — Tue, 05 Dec 2017 08:26:41 GMT

i have a requirement :

I have below data:

field1	field2
123	123:None
456	456:None
345	345:None

now the values in colfield2 should be replaced with NULL,

how to do that

Re: requirement

Anonymous — Tue, 05 Dec 2017 08:32:46 GMT

On what condition it should be null?

Re: requirement

prashantsanchet — Tue, 05 Dec 2017 08:36:55 GMT

SubField(field2,':',-1)

Re: requirement

antoniotiman — Tue, 05 Dec 2017 08:40:24 GMT

Null() as field2

Re: requirement

Anil_Babu_Samineni — Tue, 05 Dec 2017 08:45:10 GMT

Simply

Null() as FieldName OR

'' as FieldName

Not sure 100% i followed you?

Re: requirement

Anonymous — Tue, 05 Dec 2017 08:52:42 GMT

try this,,,

NullTable:

Mapping Load None , Null() autogenerate 1;

Map subfield(Field2,':',2) using NullTable;

should be loaded before your original table...

Re: requirement

Anonymous — Tue, 05 Dec 2017 08:56:23 GMT

filed2 displays value in above format against field1

so field 2 values should be null

Re: requirement

Anonymous — Tue, 05 Dec 2017 10:51:48 GMT

its not working as none alone is not the value ,

its in combination with other digits,how to write in that

Re: requirement

Anonymous — Tue, 05 Dec 2017 10:57:02 GMT

if(len(subfield(field1,:,2)),'',field1) as field2,

but ther is some mistake with this syntax ?

how to achieve the above

Re: requirement

Anonymous — Tue, 05 Dec 2017 11:01:59 GMT

this was my code in SAS which am trying to replicate in qlik:

// CASE WHEN TRIM(SUBSTRING(field2,LENGTH(field1)+2)) = 'None' THEN NULL
// ELSE TRIM(SUBSTRING(field2,LENGTH(field1)+2))
// END
// AS field2 /*

how to achieve the above

Re: requirement

Anonymous — Tue, 05 Dec 2017 11:04:51 GMT

kindly help and guide as its a urgent requirement

Re: requirement

Anonymous — Tue, 05 Dec 2017 11:13:28 GMT

if(len(subfield(field1,:,2)),'',field1) as field2, missed single quotes in the expression i.e delimiter

NullTable:

Mapping Load None , Null() autogenerate 1;

Map Field2 using NullTable;

if(len(subfield(field1,':',2)),'',field1) as field2, -- try this

Re: requirement

Anonymous — Wed, 06 Dec 2017 06:47:31 GMT

its not giving the desired output and its not taking the none value

// CASE WHEN TRIM(SUBSTRING(field2,LENGTH(field1)+2)) = 'None' THEN NULL

// ELSE TRIM(SUBSTRING(field2,LENGTH(field1)+2))

// END

// AS field2 /*

the above code needs to be replicated in qlik

Re: requirement

avinashelite — Wed, 06 Dec 2017 06:53:25 GMT

try like this

if( subfield(feild2,':',2)='None','',subfield(feild2,':',1)) as feild2

Re: requirement

Anonymous — Wed, 06 Dec 2017 07:15:40 GMT

thanks,

and if I need to replicate below in qlik:

Row_Number() OVER (PARTITION BY id ORDER BY date DESC) AS Rownum

Re: requirement

avinashelite — Wed, 06 Dec 2017 07:24:10 GMT

rowno() as Rownum

this should do

Re: requirement

Anonymous — Wed, 06 Dec 2017 07:32:01 GMT

will it be like:

Row_Number() OVER (PARTITION BY id ORDER BY date DESC) AS rowno()

but in qlik we don't have partition by and over