topic Re: Set analysis in QlikView

Set analysis

Tue, 24 Mar 2015 06:30:11 GMT

Hello All,

Stuck up with the below scenario. Hope i get help from here and proceed

Dataset:

Item	Posting Date	Type
A	24-Mar	1
B	25-Mar	1
A	25-Mar	2
C	27-Mar	1
C	28-Mar	2
B	29-Mar	2

User selects a date, we are to consider the Items of Type-1, where the posting date is on or before(until) the date selected by user.

Here, we are to make sure that there is no Type 2 happenning for that item, within the date selected by user.

Once we select all such items, we are to calculate the date selected-posting date and see if the difference is >1,

Finally, we are to count such items where the difference is greater than 1.

Eg: User selected date - 26th Mar

step1: Identify Type 1 items within 26th Mar

result: A,B

step2: Identify if the items have type2 within 26th Mar and remove them, if they have

result: A

step3: find out the date difference, user selected date-posting date

result : 2 days

step4: count the items if the difference is >1,

result: count - 1

The set analysis equivalent of this?

Thanks in advance!

Re: Set analysis

MK_QSL — Tue, 24 Mar 2015 08:04:04 GMT

Can you give one more example as step 3,4 and 5 as I am unable to understand.

Re: Set analysis

Tue, 24 Mar 2015 09:15:09 GMT

Hello Manish,

After identifying the Items in step2, here, it is a single item, item A.

we are to take the type 1 date difference for each item ( user selected date minus posting date of item A of type 1)

i.e., (26-Mar minus 24-Mar) which is 2 days

Now we have to check if we are to count the item A or not, by checking the difference is >1 or not.

Here, since the difference is 2, which is >1, we are to count item A.

Hope this clarifies.

Re: Set analysis

anbu1984 — Tue, 24 Mar 2015 09:42:38 GMT

Check this

Re: Set analysis

Tue, 24 Mar 2015 10:22:53 GMT

Clean!!

This logic should work for the given scenario, whereas.. the record count in real database is quite huge, around 60 lakh(in example, its 6 records) ..so, will the solution work in such cases as well?

And for 27th Mar.. it should eliminate item A, but it shows count as 2. can you explain this further?

Please let me know.

Thanks,

Priya

Re: Set analysis

Tue, 24 Mar 2015 10:43:41 GMT

Hii

Try this

Step1 for 26th march

= Concat(DISTINCT If([Posting Date] < Date,Item),',')

Step2

=Concat(DISTINCT {< Item=P({<[Posting Date] ={"<$(=Max(Date))"},Type = {'1'}>})>*

<Item=P({<[Posting Date] ={"<$(=Max(Date))"},Type = {'2'}>})>}Item,',')

step3

Count(If(Index(diffr,Item) And Date-[Posting Date] > 1,1))

step4:

Count(If(Index(diffr,Item) And Date-[Posting Date] > 1))

let me know.

Re: Set analysis

Tue, 24 Mar 2015 10:53:51 GMT

Hello Navven

Could you please recheck your response.

A() is not in use as far as i know.. what is that?

And what is diffr?

Thanks!

Re: Set analysis

anbu1984 — Tue, 24 Mar 2015 11:35:37 GMT

Can you explain how it will eliminate Item A if date selected is 27-Mar?

Use Distinct. You will

='Step3 & 4: '&Count(DISTINCT If(Index(vItm,Item) And Date-[Posting Date] > 1,1))

Re: Set analysis

Tue, 24 Mar 2015 11:57:39 GMT

Hello Anbu,

Step1, Step 2 are perfectly fine.

In step 3 : we are to eliminate the records identified in step2 and keep the remaining items.

in step4, for the remaining items as resulted by step 3, we are to calculate the difference of dates and if the difference is >1, we are to calculate the items.

Thanks,

Priya

Re: Set analysis

anbu1984 — Tue, 24 Mar 2015 12:08:21 GMT

In case of 27-Mar, you will get A in Step 2 and difference (27-Mar Minus 24-Mar) is 3 days which is greater than 1 and count will be 1. Is this expected your result? If yes, then try this expr

='Step3 & 4: '&Count(If(Index(vItm,Item) And Date-[Posting Date] > 1 And Type = 1,1))

Re: Set analysis

Tue, 24 Mar 2015 13:06:48 GMT

Hello Anbu..

This is not working.

We should eliminate the record identified in step2.

Your script is calculating the differnce for the date for the record which is to be excluded.

For 27-Mar, if we add the condition And Type=1, it gives correct result, but the same fails for 29-Mar.

For 28-Mar it should give 2. [Step1: A,B,C , Step2 : A , Step3: (ABC-A = B,C) , Step4: ( 28 Mar - B date, 28Mar- C date) and count how many have difference >1

Re: Set analysis

anbu1984 — Tue, 24 Mar 2015 13:43:14 GMT

Add minus instead of * in the below expr. Make this change to variable vItm

Concat(DISTINCT {< Item=P({<[Posting Date] ={"<$(=Max(Date))"},Type = {'1'}>})>

<Item=P({<[Posting Date] ={"<$(=Max(Date))"},Type = {'2'}>})>}Item,',')

If you want to include where diff is one then use >=

='Step3 & 4: '&Count(If(Index(vItm,Item) And Date-[Posting Date] >= 1 And Type = 1,1))

Re: Set analysis

Wed, 25 Mar 2015 05:13:05 GMT

This works.. Thanks for your time!!

Regards,

Priya

Re: Set analysis

Thu, 26 Mar 2015 07:39:18 GMT

Hello Anbu

Thi first step where we are using concat, is causing a performance issue

Since we have 30 lakh records.

Could you suggest any alternative for this?

Thanks!

Re: Set analysis

anbu1984 — Thu, 26 Mar 2015 08:24:23 GMT

=Count(DISTINCT {< Item=P({<[Posting Date] ={"<$(=Max(Date))"},Type = {'1'}>})>

<Item=P({<[Posting Date] ={"<$(=Max(Date))"},Type = {'2'}>})>}Item)