Script aggregation

[siddharth_kulka]

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

[Kushal_Chawda]

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

[Taoufiq_Zarra]

Hi,

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

output:

[siddharth_kulka]

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)

[Kushal_Chawda]

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

[siddharth_kulka]

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)

[Kushal_Chawda · Accepted solution]

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;

[Kushal_Chawda]

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;

[siddharth_kulka]

That worked, Cheers!

[siddharth_kulka]

Hi Taoufiq,

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