script help

[Krish2459_58]

Hi,

Please help me equivalent way to get this code.

Wildmatch(Window(Concat("term type"),"Lease ref.L"),'*Rent Indexation Review*') as Del

Thanks..

[PrashantSangle]

expression which you provided is from which tool??

what window() do??

Regards,

Prashant Sangle

[henrikalmen]

What are you trying to achieve? I don't think you can use concat within the window function, and I also wouldn't think you can use the window function within wildmatch.

[Krish2459_58]

Window is a qlik function.

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/WindowFunction....

[Krish2459_58]

Hi,

I have data as below. Here I have to exclude dates where review = Market Review.

But in the below example 24000445 shared both Initial Rent and  Market Review which I have to exclude 24000445.

lease	review	date
24000445	Initial Rent	10/12/2018
24000445	Market Review	10/12/2018
28939933	Initial Rent	09/04/2027
26907933	Market Review	05/10/2029

Required output:

lease	review	date
28939933	Initial Rent	09/04/2027

[Krish2459_58]

I used this script and it's working in qlik DEV 2024 version. But the PROD version is still 2022 version and this Window function is not available.

We planned PROD upgrade on May 2025. So I have to re-create this solution.

Data:
Load
*,
Wildmatch(Window(Concat(review),lease),'*Market Review*') as Del
Inline [
lease ,review, date
24000445, Initial Rent, 10/12/2018
24000445, Market Review, 10/12/2018
28939933, Initial Rent, 09/04/2027
26907933, Market Review, 05/10/2029]
;

NoConcatenate

Final:
Load
lease,
review,
date
Resident Data
where Del<>1;

drop table Data;

[henrikalmen]

Ah, so you can use concat within window like that - interesting!

Maybe like this to remove the window function:

load lease, review, date, wildmatch(revconcat, '*Market Review*') as Del;
left join load lease, concat(review) as revconcat group by lease;
load * inline [
lease, review, date
24000445, Initial Rent, 10/12/2018
...
];

[BPiotrowski · Accepted solution]

Hi, @Krish2459_58 

Isn't this what you are searching for ?

TMP:
NoConcatenate
Load lease as lease_tmp ,review as review_tmp, date as date_tmp Resident Data Where review = 'Market Review';

Final:
NoConcatenate
Load * Resident Data Where not Exists(lease_tmp,lease);

drop table Data,TMP;

[diegozecchini · Accepted solution]

Hi!
Maybe you can achieve this using Grouping (Concat) and Wildmatch to mark leases that contain "Market Review", and then exclude them?

Data:
LOAD * INLINE [
lease, review, date
24000445, Initial Rent, 10/12/2018
24000445, Market Review, 10/12/2018
28939933, Initial Rent, 09/04/2027
26907933, Market Review, 05/10/2029
];

TempFlag:
NoConcatenate
LOAD
lease,
Concat(review, '|') AS review_concat
Resident Data
GROUP BY lease;

Final:
NoConcatenate
LOAD
D.lease,
D.review,
D.date
Resident Data AS D
WHERE NOT EXISTS (
'FlaggedLease', lease
);

DROP TABLE Data, TempFlag;