Create category

[Manni_SM]

Hi All,

@rubenmarin  igoralcantara  Vegar

i want to create below category group in script  for session monitor application can someone help on this ?

am new to qlik

[Manni_SM]

Hi @rubenmarin   @marcus_sommer 

i tried to create bucket in front end but below condition is not working as expected 

last 60 days

last 90 days

last 180 days 

what i am missing can you suggest ?

--------------

If(num(date(LogEntryPeriodStart))<=num(date(Today()-60)) ,
'Last 60 days' ,
If(num(date(LogEntryPeriodStart))>=num(date(Today()-60) and
num(date(LogEntryPeriodStart))<=num(date(Today()-90)))
,'Last 90 Days','Last 180'))

[marcus_sommer]

I would tend to do it respectively the essential parts within the data-model whereby the following should be in general working within the UI, too:

class(floor(today() - LogEntryPeriodStart), 60)

and if there are more dimensions needed to ensure that the field-value is unique an aggr-wrapping, maybe something like:

aggr(class(floor(today() - LogEntryPeriodStart), 60), User)

If such approach returned the expected results the class-return might be adjusted in the way of the if-loop.

[rubenmarin]

Hi, the comparison should be the opposite:

If(num(date(LogEntryPeriodStart))>=num(date(Today()-60)) ,
'Last 60 days' ,...

Also, if LogEntryPeriodStart is a number, you don't really need "num(date(" to compare the value

In design you can create a table with LogEntryPeriodStart as dimension, and an expression like:

If(LogEntryPeriodStart>=Today()-60,1,2)

Sort by this measure and compare the values and check what's going on. Also sort by dimesnion to check the higher values returns '1', until it meets the 60 days criteria, then all values should return '2'.

[Manni_SM]

Hi @rubenmarin  @marcus_sommer  i tried below code to create bucket in the script as suggested by my lead but getting error  did i miss anything?

[marcus_sommer]

Because of the fact that the User_Session_Final has the same fields as the previous created tables the table won't created else these records where added to the previous ones. To prevent this behaviour you need to add a prefix, like:

...

User_Session_Final:

noconcatenate Load
...

[rubenmarin]

The error message is because of what @marcus_sommer said.

As an alternative, you can create the User_Session_final table since the begining, instead of User_Session_60Days, and instead of 90, and 180 Days table, directly concatenate in that step.

And to have all the 'buckets' fields in the same row, instead of a Concatenate you can do an outer join, or start with the 180Days table and do a left join (because the _proxySessionPackage on the 180 will have all the existing in the 90 and 60 table).

[Manni_SM]

Hi @rubenmarin  @marcus_sommer i tried using left join but the result looks odd  

User_Session_60Days_Prep:

Load

UserId,

LogEntryPeriodStart,

'1' as Counter_Session

Resident LogContent

Where num(LogEntryPeriodStart)>num(today()-60); //Formatting verification to make sure that condition works

User_Session_60Days:

Load

UserId,

    count(Counter_Session) as Bucket_60

Resident User_Session_60Days_Prep

Group by UserId;

Drop Table User_Session_60Days_Prep;

//Sessions in last 90 days

User_Session_90Days_Prep:

Load

UserId,

LogEntryPeriodStart,

'1' as Counter_Session

Resident LogContent

Where num(LogEntryPeriodStart)>num(today()-90); //Formatting verification to make sure that condition works

User_Session_90Days:

Load

UserId,

Count(Counter_Session) as Bucket_90

Resident User_Session_90Days_Prep

Group by UserId;

Drop Table User_Session_90Days_Prep;

//Sessions in last 180 days

User_Session_180Days_Prep:

Load

UserId,

LogEntryPeriodStart,

'1' as Counter_Session

Resident LogContent

Where num(LogEntryPeriodStart)>num(today()-180); //Formatting verification to make sure that condition works

User_Session_180Days:

Load

UserId,

Count(Counter_Session) as Bucket_180

Resident User_Session_180Days_Prep

Group by UserId;

Drop Table User_Session_180Days_Prep;

 NoConcatenate

User_Session_Final:

Load

*

Resident User_Session_180Days;

left join (User_Session_Final)

 Load

*

Resident User_Session_90Days;

left join (User_Session_Final)

Load

*

Resident User_Session_60Days;

Drop Tables User_Session_60Days, User_Session_90Days,User_Session_180Days;

[rubenmarin]

Hi, why you say it looks odd?

[Manni_SM]

as we have  sessions for one user is 8 .. but its showing above result that is the reason i feel it looks very odd

[marcus_sommer]

The approach to join the different buckets per User is logically wrong. The bucket-information should be better concatenated or it might be done within a single logic, like:

load
   User,
   pick(match(ceil(floor(MyDateTime - today()) / 30), 1, 2, 3, ....),
     '< 60', '< 60', '< 90', ....)
from X;

on top of it may come an aggregation-load or the count() is applied within the chart.