QlikView Table

[oanalung]

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. 

[vchuprina · Accepted solution]

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:

with selected value:

Regards,

Vitalii

[oanalung]

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]

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;

[vchuprina]

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

[oanalung]

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]

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