topic Re: Need Help in QlikView

Need Help

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

Hello Team,

Need your help on below scenario.

I have two columns like column1 and column2.In column1 we have column2 data as well like (ABC,DEF).

I tried in qlikview front end its working fine. Used wildmatch function() ▼

=IF(WildMatch(Column1,'*ABC*')=1,'ABC',IF(WildMatch(Column1,'*def*')=1,'def'))

How to achieve this in backend qlikview script?

Coumn1	Column2
Data-data-ABC-store,def-data--store1	ABC
Data-ABC-data--store	ABC
Data-data-ABC,Data-data-def-store2	ABC
def-data--store	def
Data-data-def-store	def
Data-data-def-def,Data-ABC-data--store	def

Results:

Coumn1	Column2
Data-data-ABC	ABC
Data-ABC-data--store	ABC
Data-data-ABC	ABC
def-data--store	def
Data-data-def-store	def
Data-data-def-def	def
def-data--store1	def
Data-data-def-store2	def

Re: Need Help

dapostolopoylos — Mon, 30 Jul 2018 07:33:31 GMT

You can use the exact same function syntax in the backend somehow like that:

LOAD

Column1,

Column2,

IF(WildMatch(Column1,'*ABC*')=1,'ABC',IF(WildMatch(Column1,'*def*')=1,'def')) as NewColum

FROM

File;

Re: Need Help

tresB — Mon, 30 Jul 2018 07:40:21 GMT

It seems that you need to consider that key which come first. If so, try like:

IF(Index('Column1','ABC')< Index('Column1','def'),'ABC','def') as Column2

Re: Need Help

gireesh1216 — Mon, 30 Jul 2018 07:49:33 GMT

Thanks. But here(IF(WildMatch(Column1,'*ABC*')=1,'ABC',IF(WildMatch(Column1,'*def*')=1,'def')) as NewColum) we are manually writing 'ABC','def' values in script.I don't want to write manually. If any new data comes in colum2,above formula not useful.

I am looking dynamically.

Re: Need Help

gireesh1216 — Mon, 30 Jul 2018 07:50:02 GMT

we are manually writing 'ABC','def' values in script.I don't want to write manually. If any new data comes in colum2,above formula not useful.

I am looking dynamically.

Re: Need Help

tresB — Mon, 30 Jul 2018 07:59:01 GMT

What is the logic of defining 'ABC' or 'def' to be considered in column2? Is it like: anything apart from 'data' and 'store' ?

Re: Need Help

fabdulazeez — Mon, 30 Jul 2018 08:18:22 GMT

how do you want to order it, Like first ABC than def

Re: Need Help

gireesh1216 — Mon, 30 Jul 2018 09:00:34 GMT

anything apart from 'data' and 'store' ?---->No..

Column2 data present in column1 data. based on that we have to find out.

Example: Data-data-ABC

Above example ABC is column2 data.

Re: Need Help

gireesh1216 — Mon, 30 Jul 2018 09:02:52 GMT

Hello Fawaz Abdul Azeez ,

Any order I am okay.

I am expecting required output.

Re: Need Help

MindaugasBacius — Mon, 30 Jul 2018 09:20:58 GMT

May be this is what you are looking for?

map1:

mapping load * inline [

x, y

data,

store,

-, ] ;

tmp:

LOAD *

,PurgeChar(MapSubString('map1', Column1), '0123456789') as Column2

INLINE [

Column1

data-ABC-data2-

store-data-def

DRT-data3-store2

];

Re: Need Help

tresB — Mon, 30 Jul 2018 10:01:21 GMT

One solution, perhaps could be like:

Source:

Load

SubField(Column1, ',') as Column1,

Column2

Inline [

Column1| Column2

Data-data-ABC-store,def-data--store1| ABC

Data-ABC-data--store| ABC

Data-data-ABC,Data-data-def-store2| ABC

def-data--store| def

Data-data-def-store| def

Data-data-def-def,Data-ABC-data--store| def] (delimiter is '|');

Join (Source)

Load Distinct Column2 as Column3 Resident Source;

NoConcatenate

Final:

Load

Column1,

Column3 as Column2

Resident Source Where Index(Column1, Column3);

Drop table Source;

Re: Need Help

fabdulazeez — Mon, 30 Jul 2018 10:32:51 GMT

INPUT:

Load * Inline

[Column1, Column2

'Data-data-ABC-store,def-data--store1','ABC'

'Data-data-ABC,Data-data-def-store2', 'ABC'

'def-data--store', 'def'];

INPUT2:

Load

concat( chr(39) & '*'&Column2&'*' & chr(39) , ',') as input

Resident INPUT;

Let vInput= peek('input',-1,'INPUT2');

INPUT3:

Load

Column1,

WildMatch(Column1,$(vInput)) as test,

wildmatch( 'fex', 'ja*','fe?','mar') as test2

Resident INPUT;