topic Re: script help in App Development

script help

Krish2459_58 — Fri, 28 Feb 2025 06:14:39 GMT

Hi,

Please help me equivalent way to get this code.

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

Thanks..

Re: script help

PrashantSangle — Fri, 28 Feb 2025 06:38:55 GMT

expression which you provided is from which tool??

what window() do??

Regards,

Prashant Sangle

Re: script help

henrikalmen — Fri, 28 Feb 2025 06:51:42 GMT

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.

Re: script help

Krish2459_58 — Fri, 28 Feb 2025 07:09:27 GMT

Window is a qlik function.

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/WindowFunctions/Window.htm#:~:text=Window()%20performs%20calculations%20from,or%20minimum%20within%20the%20column.

Re: script help

Krish2459_58 — Fri, 28 Feb 2025 07:10:29 GMT

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

Re: script help

Krish2459_58 — Fri, 28 Feb 2025 07:20:55 GMT

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;

Re: script help

henrikalmen — Fri, 28 Feb 2025 07:30:55 GMT

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
...
];

Re: script help

BPiotrowski — Fri, 28 Feb 2025 08:50:59 GMT

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;

Re: script help

diegozecchini — Fri, 28 Feb 2025 10:03:24 GMT

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;