Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
oanalung
Contributor III
Contributor III

QlikView Table

hi,

I have a table and I would like to include only user ids that reached Stage 3(users can go into Stage 1, then Stage 2 and so on, but it's possible a user stages stops at Stage 2. however, I would like to make sure I have only users who went to Stage  1, Stage 2 and Stage 3). One user can't get to Stage 3 without going into Stage 1 and then Stage 2. 

 

stage.PNG

Labels (3)
15 Replies
vchuprina
Specialist
Specialist

Hi,

Looks like you have spaces in the stage field, so first, you should prepare your data, for instance, remove text and spaces.

Try the following:

//Create a new field that contains only the numeric part of the stage field
Data:
LOAD
     *,
     KeepChar(stage, '0123456789 ') AS StageNum;
Load * inline [
UserID,UserName,date,stage
1, abc, 2022-01-01, Stage1
1, abc, 2022-02-01, Stage2
1, abc, 2022-05-01, Stage3
1, abc, 2022-05-01, Stage4
2, hgb, 2022-01-01, Stage1
2, hgb, 2022-02-01, Stage2
2, hgb, 2022-05-01, Stage3
2, hgb, 2022-05-01, Stage 4
3, hgh, 2022-01-01, Stage1
3, hgh, 2022-02-01, Stage2
3, hgh, 2022-05-01, Stage3
4, gdf, 2022-05-01, Stage 1
];

//Find max StageNum for each user
//Join UserMaxStage to Data table
//UserMaxStage can be used as a filter in set analysis
Left Join(Data)
MaxStage:
LOAD
     UserID,
     Max(StageNum) AS UserMaxStage
Resident Data
Group By UserID;

//Drop StageNum field from the Data Model
DROP FIELD StageNum;

Result:

vchuprina_0-1651610414988.png

with selected value:

vchuprina_1-1651610475433.png

 

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
oanalung
Contributor III
Contributor III
Author

it worked with this and creating another values for that Flag: 

Left Join([YOUR table])
Flag:
LOAD
     UserID,
     if(MaxStage=3 ,1

            if(MaxStage=4,2)) as Stage Flag);

Load 

UserId

max(stage) as MaxStage 

Resident(your table) 

group by UserId

but if I want to add another condition in one of the if(like date>'2000-01-01') and I add the date in my Load, it needs to be added in the group by and it looks like it's changing the number of rows in all my reports. 

any idea how to avoid that? I just want to build another value for that Flag(ex:5, when stage=5 and date follows a condition) 

 

thank you

 


Where MaxStage = 'Stage3';
LOAD
     UserID,
     Max(Stage) AS MaxStage
Resident [YOUR table]
Group By UserID
;

vchuprina
Specialist
Specialist

Hi,

First solution

Once you join the UserMaxStage field to the DataTMP table you can make resident load and add any flag that you need based on date and UserMaxStage fields

DataTMP:
LOAD
     *,
     KeepChar(stage, '0123456789 ') AS StageNum;
Load * inline [
UserID,UserName,date,stage
1, abc, 2022-01-01, Stage1
1, abc, 2022-02-01, Stage2
1, abc, 2022-05-01, Stage3
1, abc, 2022-05-01, Stage4
2, hgb, 2022-01-01, Stage1
2, hgb, 2022-02-01, Stage2
2, hgb, 2022-05-01, Stage3
2, hgb, 2022-05-01, Stage 4
3, hgh, 2022-01-01, Stage1
3, hgh, 2022-02-01, Stage2
3, hgh, 2022-05-01, Stage3
4, gdf, 2022-05-01, Stage 1
];

//Find max StageNum for each user
//Join UserMaxStage to Data table
//UserMaxStage can be used as a filter in set analysis
Left Join(DataTMP)
MaxStage:
LOAD
     UserID,
     Max(StageNum) AS UserMaxStage
Resident DataTMP
Group By UserID;

//Drop StageNum field from the Data Model
DROP FIELD StageNum;

Data:
LOAD
     UserID,
     UserName,
     date,
     stage,
     UserMaxStage,
     IF(UserMaxStage = 4 and date> '2000-01-01', 1, 0) AS SomeFlag
Resident DataTMP;

DROP TABLE DataTMP;

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
vchuprina
Specialist
Specialist

Second solution:

You can use set analysis to show necessary data in the charts, for instance:

Expression:

SUM({<UserMaxStage={3}, date={">=2022-02-01"}>}value)

will show the same data in the chart as a condition:

IF(UserMaxStage = 3 and date >= '2022-02-01', value) as value

 

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
oanalung
Contributor III
Contributor III
Author

It's not a solution for me to use Load INLINE, 

 

is there a way to keep my preceding load and left join with flag table? 

I have tried loading the user id, date into another LOAD but it doesn't work

vchuprina
Specialist
Specialist

I use inline just for example because I don't have your data source.

I guess you should just replace the Inline table with your data source.

For instance, if your data source is an excel file then replace inline load 

DataTMP:
LOAD
     *,
     KeepChar(stage, '0123456789 ') AS StageNum;
Load * inline [
UserID,UserName,date,stage
1, abc, 2022-01-01, Stage1
1, abc, 2022-02-01, Stage2
1, abc, 2022-05-01, Stage3
1, abc, 2022-05-01, Stage4
2, hgb, 2022-01-01, Stage1
2, hgb, 2022-02-01, Stage2
2, hgb, 2022-05-01, Stage3
2, hgb, 2022-05-01, Stage 4
3, hgh, 2022-01-01, Stage1
3, hgh, 2022-02-01, Stage2
3, hgh, 2022-05-01, Stage3
4, gdf, 2022-05-01, Stage 1
];

By

DataTmp:

LOAD
       UserID,
       UserName,
       date,
       stage,

       KeepChar(stage, '0123456789 ') AS StageNum
FROM
[C:\Users\chuprynav\Downloads\Example.xlsx]
(ooxml, embedded labels, table is [Sheet1]);

 

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").