Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
siddharth_kulka
Creator II
Creator II

Script aggregation

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 IDLocationExpected Output (Final Location)
1USUS
1JapanUS
1InternationalUS
2JapanJapan
2InternationalJapan
3USUS
3InternationalUS
Labels (2)
1 Solution

Accepted Solutions
Kush
MVP
MVP

 

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;

 

View solution in original post

9 Replies
Kush
MVP
MVP

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

Taoufiq_Zarra
Master II
Master II

Hi,

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

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
siddharth_kulka
Creator II
Creator II
Author

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)

Kush
MVP
MVP

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

siddharth_kulka
Creator II
Creator II
Author

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)

Kush
MVP
MVP

 

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;

 

View solution in original post

Kush
MVP
MVP

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
Creator II
Creator II
Author

That worked, Cheers!

siddharth_kulka
Creator II
Creator II
Author

Hi Taoufiq,

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