topic Re: Script aggregation in QlikView

Script aggregation

siddharth_kulka — Sat, 16 Nov 2024 01:36:53 GMT

Hello,

Stuck with a problem statement which seemed relatively straightforward at the outset. Need to implement in the script (Not UI). Any ideas would be helpful. Cheers!

Problem Statement:

If Location='US','US',If Location='Japan,'Japan',If Location='International','International') as Final Location

Group by Trip ID

Below doesn't work as group by requires an aggregation function:

Test:
load
if([Location]='Domestic','US',
if([Location]='Japan','Japan',
if([Location]='International','International'))) as [Final Location],
TripID
resident Travel
group by
TripID;

Source Data

Travel:

Trip ID	Location	Expected Output (Final Location)
1	US	US
1	Japan	US
1	International	US
2	Japan	Japan
2	International	Japan
3	US	US
3	International	US

Re: Script aggregation

Kushal_Chawda — Tue, 31 Dec 2019 17:44:10 GMT

Seems confusing. If Location='Japan' then how expected output is US?

Re: Script aggregation

Taoufiq_Zarra — Tue, 31 Dec 2019 18:04:54 GMT

Hi,

if you consider each time the first registration as a new location, I suggest the attached solution

output:

Re: Script aggregation

siddharth_kulka — Tue, 31 Dec 2019 18:22:36 GMT

Hi -

To clarify, First registration of a new location is not the criteria to designate the final location.

For a Trip ID (E.g 1)

If US is one of the 3 values, then Final Location = 'US'

else if

Japan is one of the 3 values, then Final Location = 'Japan' (as US was not one of the 3 values)

else 'Internation' (as US and Japan were not one of the 3 values)

Re: Script aggregation

Kushal_Chawda — Tue, 31 Dec 2019 18:24:56 GMT

What if have both Japan & US as one of the value?

Re: Script aggregation

siddharth_kulka — Tue, 31 Dec 2019 18:32:12 GMT

if both Japan & US are one of the values then 'US'

Below is the requirement:

If US is one of the 3 values, then Final Location = 'US'

else if

Japan is one of the 3 values, then Final Location = 'Japan' (as US was not one of the 3 values)

else 'International' (as US and Japan were not one of the 3 values)

Re: Script aggregation

Kushal_Chawda — Tue, 31 Dec 2019 19:10:25 GMT

You can do something like below T1: LOAD "Trip ID", Location FROM [lib://Qlik web] (html, utf8, embedded labels, table is @1); T2: Load *, if(WildMatch(lower(LocationAll),'*|us|*'),'US', if(WildMatch(lower(LocationAll),'*|japan|*'),'Japan',purgechar(LocationAll,'||'))) as LOCATION; Load "Trip ID", Concat(DISTINCT '|'&trim(Location)&'|',',') as LocationAll Resident T1 Group by "Trip ID"; Drop Field LocationAll;

Re: Script aggregation

Kushal_Chawda — Tue, 31 Dec 2019 19:17:11 GMT

Another approach and also see first approach with slight modification

Map_location: mapping Load lower(trim(Location)) as Location,Flag Inline [ Location, Flag US,1 Japan,2 ]; T1: LOAD "Trip ID", Location, ApplyMap('Map_location',lower(trim(Location)),3) as LocationFlag FROM [lib://Qlik web] (html, utf8, embedded labels, table is @1); T2: Load *, if(RowNo()=1 or "Trip ID"<>Previous("Trip ID"), if(LocationFlag=1,'US',if(LocationFlag=2,'Japan',Location)), if("Trip ID"= Previous("Trip ID") and (Previous(LocationFlag)=1 or Previous(LocationFlag)=2), Peek(LOCATION),Location)) as LOCATION Resident T1 Order by "Trip ID",LocationFlag; Drop Table T1;

Re: Script aggregation

siddharth_kulka — Tue, 31 Dec 2019 20:12:37 GMT

That worked, Cheers!

Re: Script aggregation

siddharth_kulka — Tue, 31 Dec 2019 20:16:27 GMT

Hi Taoufiq,

This approach is also expected to work after a few tweaks, so thank you.