Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. 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.