Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rohitk1609
Master
Master

How to replace particular pattern string from array string?

Hi techies,

I have use case where I need to replace values before 2018-01-01 from string in array form:

rohitk1609_0-1615110050954.png

Values like 2017-01-01 or 2016-05-01 should be removed from above image array kind of string. 

Labels (1)
  • array

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Data:
LOAD
'''' &
concat(ADate, ''',''') & '''' as Array
Where ADate >= MakeDate(2018)
Group by RecId
;
LOAD
RecId,
Date#(TextBetween(ADate, '''', ''''), 'YYYY-MM-DD') as ADate
;
LOAD
RecId,
SubField(Array, ',') as ADate
;
LOAD *, RecNo() as RecId Inline [

Array
"'2021-02-01','2021-03-01','2020-11-10','2017-03-04','2016-02-01','2020-05-01'"
"'2020-02-01','2021-04-01','2020-11-10','2014-03-04'"
]
; 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

View solution in original post

3 Replies
eliran
Creator III
Creator III

Hi,

 

What are you doing with this array? if it should remain as array, you can load it into rows, then use a filter on these rows, and concat it to array again.

I hope it helps,

Eliran.

Vegar
MVP
MVP

If you need to evaluate the array value before deciding to replace it or not then you could consider @eliran suggestion. You could implement it somewhat like this.

TMP:

Load RowID, subfield(ArrayField, ';') as Value 

From source;

 

Final:

Load RowID, concat(Value, ';') as ArrayField

From TMP

Where %YOUR_CRITERIA%;

Drop table TMP;

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Data:
LOAD
'''' &
concat(ADate, ''',''') & '''' as Array
Where ADate >= MakeDate(2018)
Group by RecId
;
LOAD
RecId,
Date#(TextBetween(ADate, '''', ''''), 'YYYY-MM-DD') as ADate
;
LOAD
RecId,
SubField(Array, ',') as ADate
;
LOAD *, RecNo() as RecId Inline [

Array
"'2021-02-01','2021-03-01','2020-11-10','2017-03-04','2016-02-01','2020-05-01'"
"'2020-02-01','2021-04-01','2020-11-10','2014-03-04'"
]
; 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com