topic Re: Set Analysis challenge in QlikView

Set Analysis challenge

Anonymous — Wed, 16 Dec 2015 10:18:13 GMT

I have a table that has transactions for different shops and another table that contains the closing times for these shops. I need to count number of transactions occurred after closing time.

Below is the straight table I want

ShopNo	No. of transactions after closing time
1	58
2	52
3	88

Now the problem I am getting is in my set analysis expression where I am using maxstring function. This Maxstring function is ignoring the ShopNo dimension in my straight table. Below is my expression:

=count({$<Transaction_Time={">$(=maxstring({<DaysClose={'$(=WeekDay&' Close')'}>}ClosingTime))"}>}TransactionID)

The expression works fine as long as I've selected a shop number but it gives me 0 if non of the shops are selected.

Transaction_Time is the time of transaction
DaysClose is coming from the table that contains the day name, shop no. and closing time.
ClosingTime is the closing time of shop
TransactionID is what I need to count
WeekDay is the day Name

For your ease below is the table that contains closing time with shop no and days

ShopNo	DaysClose	ClosingTime
1	Mon Close	17:30:00
1	Tue Close	17:30:00
1	Wed Close	17:00:00

I've looked into the problem in detail and what I've found is that the maxstring function is ignoring the Shop No dimension of my straight table. is there anyway I can include the ShopNo in my Maxstring function? so I can see all the shops with transactions outside closing time?

Re: Set Analysis challenge

marcus_malinow — Wed, 16 Dec 2015 10:28:59 GMT

This looks like something that could be solved using the aggr function. However it's difficult to give you the answer unless you attach a version of your application.

Re: Set Analysis challenge

sunny_talwar — Wed, 16 Dec 2015 10:34:45 GMT

I agree with Marcus. A sample here would be very helpful.

Re: Set Analysis challenge

tresB — Wed, 16 Dec 2015 10:38:38 GMT

try to share a sample qvw. By the time, try using a variable like:

vMaxStr= maxstring({<DaysClose={'$(=WeekDay&' Close')'}>}ClosingTime)

=count({$<Transaction_Time={">$(vMaxStr)"}>}TransactionID)

Re: Set Analysis challenge

Anonymous — Wed, 16 Dec 2015 15:57:02 GMT

I've tried this but this didn't work.

The problem I'm having is that when I put this expression against ShopNo it gives me the closing time fine but as soon as I put it in my set analysis expression the result is always zero:

Aggr(minstring({<DaysClose={"$(=WeekDay&' Close')"}>}ClosingTime)&':00',ShopNo)

But when I put the above expression in the main set analysis expression to get the closing time, it doesn't work:

=count({$<Transaction_Time={">$(=Aggr(maxstring({<DaysClose={'$(=WeekDay&' Close')'}>}ClosingTime)&':00',ShopNo))"}>}TransactionID)

Re: Set Analysis challenge

maxgro — Wed, 16 Dec 2015 18:44:34 GMT

I would add a flag in the script for "closing time" transaction id

Re: Set Analysis challenge

Anonymous — Wed, 16 Dec 2015 21:02:50 GMT

I think the problem is the dynamic nature of closing time for each ShopNo.

The following expression works perfectly fine as long as I put it in straight table and against ShopNo as dimension.

maxstring({<DaysClose={"$(=WeekDay&' Close')"}>}ClosingTime)

But I'm failing to use the same expression within count expression below:

=count({$<Transaction_Time={">$(=maxstring({<DaysClose={'$(=WeekDay&' Close')"}>}ClosingTime))'}>}TransactionID)

now the above count expression has to get dynamic closing time for each shop and I think this dynamic nature of the above expression is failing.

Does anyone has any idea how to resolve this please?

Re: Set Analysis challenge

Anonymous — Wed, 16 Dec 2015 21:03:13 GMT

Could you please elaborate a bit more on this?

Re: Set Analysis challenge

Anonymous — Wed, 16 Dec 2015 22:24:57 GMT

Hi Everyone,

I've attached the application for your review. Looks like it's working but now how I want it to work.

The problem is, the second expression is picking up the maximum time for both shops. This needs to by dynamic. closing time for Tuesday 13 Jan 2015 is different for both shops so the no. of transactions should be 24 for shop 1 and 23 for shop 2 not 19 for shop 1.

Another problem is that I have to select a date to see the number of transactions. Can the expression aggregate all the transactions outside closing hours for all days?

Re: Set Analysis challenge

Anonymous — Wed, 16 Dec 2015 22:26:58 GMT

Hi Marcus,

I've attached the application for your review. Looks like it's working but not how I want it to work.

Another problem is that I have to select a date to see the number of transactions. Can the expression aggregate all the transactions outside closing hours for all days?

Re: Set Analysis challenge

Thu, 17 Dec 2015 07:45:29 GMT

Hi,

Join two tables using ShopNo and DaysClose to get ClosingTime instead of ShopNo.

LOAD [Shop No],
[Shop No]&DaysClose as Key,
     time(ClosingTime,'hh:mm:ss') as ClosingTime,
     DaysClose
FROM
[SetAnalysisChallenge.xls]
(biff, embedded labels, table is Timings$);
Transactions:
LOAD [Shop No]&WeekDay(TransactionDate) as Key,
     TransactionTime,
     TransactionDate,
     TransactionID,
     WeekDay(TransactionDate) as Week_Day
FROM
[SetAnalysisChallenge.xls]
(biff, embedded labels, table is Transactions$);

Use the following expression to get the count of transactions after closing time.

count({<TransactionTime={">$(=maxstring(total <[Shop No],TransactionDate> ClosingTime))"}>}TransactionID)

Hope it helps.

Re: Set Analysis challenge

marcus_malinow — Thu, 17 Dec 2015 09:49:11 GMT

Ok,

looking at your data model I have a suggestion. It won't immediately solve this problem, but will get us one step closer.

I'd suggest instead of linking Timing and Calendar to Data, set up a link table. At the moment there's no link between DaysClose and WeekDay, and really there should be.

If you need help with this, upload your test data sources.

Marcus

Re: Set Analysis challenge

Anonymous — Thu, 17 Dec 2015 10:38:05 GMT

Thanks Marcus for your reply.

I've attached the data source file for you.

Re: Set Analysis challenge

sasikanth — Thu, 17 Dec 2015 12:40:38 GMT

HI,

Since both these fields Closing time and Transaction time are in same table,

Try to create a flag at script level

Load *,

if(Closing_time<Transaction_time,1,0) as Trans-Flag FROM Table;

Handle if any formatting issues

Use this in your table

=Count({<Trans_Flag={1}>}TransactionID)

Re: Set Analysis challenge

Kushal_Chawda — Thu, 17 Dec 2015 14:05:38 GMT

You can handle it in script itself

Timing:

mapping LOAD ShopNo&DaysClose as key,

time(ClosingTime,'hh:mm:ss') as ClosingTime

FROM

[ShopClosingTime.xlsx]

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD *,

if(frac(Transaction_Time)>=Frac(ClosingTime),1,0) as Flag;

LOAD *,

ApplyMap('Timing',ShopNo&WeekDay,null()) as ClosingTime;

LOAD ShopNo,

TransactionID,

time(Transaction_Time,'hh:mm:ss') as Transaction_Time,

TransactionDate,

WeekDay(TransactionDate) as WeekDay

FROM

[ShopClosingTime.xlsx]

(ooxml, embedded labels, table is Transactions);

Now you can simply write the expression as

=count({<Flag={"1"}>}TransactionID)