topic Re: Using date present as Headers(Columns) in QlikView

Using date present as Headers(Columns)

Pulkit_Thukral — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a requirement where the data is in the below format:

Node	12-Nov	13-Nov	14-Nov	15-Nov	16-Nov
1	success: 1	success: 1	success: 1	failed: 1	failed: 1
2	success: 1	success: 1	success: 1	failed: 1	failed: 1
3	success: 1	success: 1	success: 1	failed: 1	failed: 1
4	success: 2	success: 2	success: 2	failed: 1	failed: 1
5	success: 2	success: 2	success: 2	failed: 1	failed: 1
6	success: 1	success: 1	success: 1	failed: 1	failed: 1
7	success: 1	success: 1	success: 1	success: 1	success: 1
8	success: 2	success: 2	success: 2	success: 2	success: 2
9	success: 3	success: 3	success: 3	success: 3	success: 3
10	success: 3	success: 3	success: 3	success: 3	success: 3

As you may notice DATE is present as Column Header.

Now requirement is to pick out only last 2 dates i.e. 15th Nov and 16th Nov from above data and then check its content :

if last 2 dates have '*Failed*' as the value ,then that node will be considered as a faulty and a report would be generated for such nodes.

From above data,following should be the output in qlikview:

Node	15-Nov	16-Nov
1	failed: 1	failed: 1
2	failed: 1	failed: 1
3	failed: 1	failed: 1
4	failed: 1	failed: 1
5	failed: 1	failed: 1
6	failed: 1	failed: 1

How can this be done in qlikview ?

Re: Using date present as Headers(Columns)

tresB — Fri, 18 Nov 2016 09:28:22 GMT

May be like in attached.

Re: Using date present as Headers(Columns)

tracysmart — Fri, 18 Nov 2016 09:47:20 GMT

You can use Set Analysis in your expression to limit to the last 2 dates and only where it is failed. So it will be something like this

maxstring({<Date={'>=$(=max(Date)-1)'},Data={'failed: 1'} >}Data)

Re: Using date present as Headers(Columns)

Pulkit_Thukral — Fri, 18 Nov 2016 10:06:23 GMT

Hi Tresesco,

How are you taking "Date" Field in the data ?

Re: Using date present as Headers(Columns)

tresB — Fri, 18 Nov 2016 10:14:02 GMT

In crosstable() third parameter is number of fields qualifies . And the default is 1. That means here your first field is being picked silently. Learn about crosstable().

Re: Using date present as Headers(Columns)

Pulkit_Thukral — Fri, 18 Nov 2016 11:09:54 GMT

Hi Tracy,

Date Range would have to be the Primary Measure for calculation.

Example:- Another report- last 5 days Failure report would have only those Nodes listed where last 5 days data is present as Failed .Even is the ratio is 4:1 where last 4 days are Failure and last 5th day is a success, then also that node will be excluded from the list.

Not sure if this is covered in your expression ?

Re: Using date present as Headers(Columns)

MarcoWedel — Sat, 19 Nov 2016 00:21:34 GMT

Hi,

maybe one solution could be also:

If(Max({$<Date={"=Date>=Max(Total Date)-1"}>} NodeState like '*failed*'), 'faulty', 'ok')

tabTemp:
CrossTable (DateTemp, NodeState)
LOAD * FROM [https://community.qlik.com/thread/240229] (html, codepage is 1252, embedded labels, table is @1) Where Node;
Join
LOAD Distinct
    DateTemp,
    Date(Date#(DateTemp&Year(Today()),'DD-MMMYYYY')) as Date
Resident tabTemp;

hope this helps

regards

Marco