topic Re: QlikView Table in QlikView

QlikView Table

oanalung — Thu, 28 Apr 2022 12:23:48 GMT

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.

Re: QlikView Table

marcus_sommer — Thu, 28 Apr 2022 13:50:05 GMT

Is it related to the script or the UI?

- Marcus

Re: QlikView Table

oanalung — Thu, 28 Apr 2022 13:51:51 GMT

hi, not sure if it can be done in the UI, I guess only in the script.

I want to select users that went to all 3 stages and make sure only those users will be visible in that Table Box.

Thank you

Re: QlikView Table

marcus_sommer — Thu, 28 Apr 2022 14:13:06 GMT

Both ways should be possible - in script:

temp: load distinct UserID from X where stage = 'Stage3';

final: load *, exists(UserID) as StageFlag from X;

and the flag could be then used as selection or set analysis condition.

Within the UI you may also apply something like this:

count({< UserID = p({< stage = {'Stage3'}>} UserID)>} UserName)

- Marcus

Re: QlikView Table

oanalung — Thu, 28 Apr 2022 14:44:32 GMT

I already have this in the script.

Load

user_id as [User ID],

user_name as [User Name]

stage as [Stage],

date as [Date];

SQL

SELECT

user_id,

username,

stage,

date

from table_name; //data extracting from Redshift

Should this go before my script?

Re: QlikView Table

marcus_sommer — Thu, 28 Apr 2022 14:53:28 GMT

By using exists() the load-order within the script is important because the check happens against all already loaded field-values. This means you need to control the load-order whereby they mustn't be mandatory changed because you could also use a (multiples) renaming of the field and/or using exists() with two parameters. In regard to my example from above it may look like:

temp: load distinct UserID as UserIDForExists from X where stage = 'Stage3';

final: load *, exists(UserIDForExists, UserID) as StageFlag from X;

- Marcus

Re: QlikView Table

vchuprina — Thu, 28 Apr 2022 15:04:51 GMT

Hi,

You can add this after your load to get a flag

Left Join(YOUR table)
Flag:
LOAD Distinct
UserID,
'1' AS StageFlag
Resident YOUR table
Where Stage= 'Stage3'
;

Regards,

Vitalii

Re: QlikView Table

oanalung — Tue, 03 May 2022 12:52:03 GMT

This can work, but it will also include users who reached Stage 4 - but I only want users who reached Stage 3 and stopped there.

Re: QlikView Table

vchuprina — Tue, 03 May 2022 13:07:05 GMT

Hi,

In this case try this, first you need to find max stage and then use it as a Filter

Left Join([YOUR table])
Flag:
LOAD
     UserID,
     1 as StageFlag
Where MaxStage = 'Stage3';
LOAD
     UserID,
     Max(Stage) AS MaxStage
Resident [YOUR table]
Group By UserID
;

Regards,

Vitalii

Re: QlikView Table

oanalung — Tue, 03 May 2022 14:50:50 GMT

This is actually working, can this be achieved to create another pivot table where I can include only users that have reached Stage 4? (mentioning that a user can go above Stage 4 like Stage 5, Stage 6)

If I do another join where max(stage)=stage 4 is not working

Re: QlikView Table

vchuprina — Tue, 03 May 2022 20:38:54 GMT

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

Re: QlikView Table

oanalung — Wed, 04 May 2022 19:08:35 GMT

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
;

Re: QlikView Table

vchuprina — Wed, 04 May 2022 20:35:17 GMT

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;

Re: QlikView Table

vchuprina — Wed, 04 May 2022 20:45:45 GMT

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

Re: QlikView Table

oanalung — Thu, 05 May 2022 07:16:45 GMT

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

Re: QlikView Table

vchuprina — Thu, 05 May 2022 07:25:51 GMT

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
UserID,
UserName,
date,
stage,

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

Regards,

Vitalii