topic Re: Multiple Dates in Qlikview Apps in QlikView

Multiple Dates in Qlikview Apps

Anonymous — Wed, 16 Jul 2014 08:25:59 GMT

Hello All,

I am working on a QV application where I am having multiple date for multiple analysis say,

Admission Date for Admission Analysis

Drop Out Date for Drop Out Analysis

Pass Out Date for Pass Out Analysis

Attendance Date for Attendance Analysis

Is this possible to combine all these dates and make a unique date from these dates to use in QV application?

Regards,

Balraj

Re: Multiple Dates in Qlikview Apps

ashfaq_haseeb — Wed, 16 Jul 2014 08:28:33 GMT

Hi,

Look at the below post

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Regards

ASHFAQ

Re: Multiple Dates in Qlikview Apps

Anonymous — Wed, 16 Jul 2014 09:10:24 GMT

Ashfaq,

Could you help me to understand this syntax:

Applymap('OrderID2OrderDate',OrderID,Null()) as CanonicalDate

Regards,

Balraj

Re: Multiple Dates in Qlikview Apps

ashfaq_haseeb — Wed, 16 Jul 2014 09:16:01 GMT

HI,

Load OrderLineID, Applymap('OrderID2OrderDate',OrderID,Null()) as CanonicalDate, 'Order' as DateType

Resident OrderLines;

It will map OrderID null if not find then it will map with Order date

Regards

ASHFAQ

Re: Multiple Dates in Qlikview Apps

Anonymous — Wed, 16 Jul 2014 09:24:50 GMT

So, you mean to say:

Null() will keep the OrderID null and if OrderID is not null than it will map only OrderDate

Is it so?

In my case, it is keeping all the values null except one date say Shipped date only...

Why It is so?

Regards,

Balraj

Re: Multiple Dates in Qlikview Apps

Anonymous — Wed, 16 Jul 2014 09:45:52 GMT

Applymap('OrderID2OrderDate',OrderID,Null())

What is this 'OrderID2OrderDate'?

As per Applymap() syntax, it should be table name.

Regards,

Balraj

Re: Multiple Dates in Qlikview Apps

its_anandrjs — Wed, 16 Jul 2014 09:50:37 GMT

In the Expression Applymap('OrderID2OrderDate',OrderID,Null())

1. 'OrderID2OrderDate' is a table name (Mapping Table)

2. OrderID is a Field name

3. Null() is used if some value not matched with the OrderID then fill with the Null values

Re: Multiple Dates in Qlikview Apps

parthiband — Wed, 16 Jul 2014 09:58:00 GMT

Hi Balraj,

Applymap('MapName','Key Field used to map a value','If Key field value not found,then use the value here')

MapName (OrderID2OrderDate) ->

Its nothing but a mapping table name.

Key Field used to map a value (OrderID) ->

In the Mapping table(OrderID2OrderDate) you can have ONLY two fields(Field1 and Field2) and the first field SHOULD be the KEY FIELD ie., OrderID in your case.

If u use ApplyMap() function, it searches for the Key field in the target table where u use the applymap function and deploy the values(Field2).

If the Field1(KeyField) is not found, by default it will put the Keyfield value.

Else, if u given as null() or any text, it will be displayed over there.

Thanks

Re: Multiple Dates in Qlikview Apps

Anonymous — Wed, 16 Jul 2014 10:10:26 GMT

Hello,

LinkTable:

load #StudentID_LinkTable,

DateOfAdmission as LinkDate,

'Admission' as DateType

Resident StudentMaster;

load #StudentID_LinkTable,

ApplyMap('StudentMaster',%ARStudentID, null()) as LinkDate,

'Alumnis' as DateType

Resident StudentMaster;

load #StudentID_LinkTable,

ApplyMap('StudentMaster',#ARStudentID, null()) as LinkDate,

'DropOut' as DateType

Resident StudentMaster;

In my case, except Admission all values are null.

Why it is so?

Regards,

Balraj

Re: Multiple Dates in Qlikview Apps

parthiband — Wed, 16 Jul 2014 10:52:46 GMT

Except 'Admission' no data will be having those student id or

Your ApplyMap() function might be wrong

If u say, the above is ur script.

I will say, it is wrong.

The script should be,

LinkTable:

load #StudentID_LinkTable,

DateOfAdmission as LinkDate,

'Admission' as DateType

Resident StudentMaster;

load #StudentID_LinkTable,

ApplyMap('StudentMaster',#StudentID_LinkTable, null()) as LinkDate,

'Alumnis' as DateType

Resident StudentMaster;

load #StudentID_LinkTable,

ApplyMap('StudentMaster',#StudentID_LinkTable, null()) as LinkDate,

'DropOut' as DateType

Resident StudentMaster;

Thanks,

Re: Multiple Dates in Qlikview Apps

Anonymous — Wed, 16 Jul 2014 11:01:47 GMT

Parthiban,

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

As per above article, It should be a connecting field which is OrderID.

In my case, it is %ARStudentID.

Regards,

Balraj

Re: Multiple Dates in Qlikview Apps

Anonymous — Wed, 16 Jul 2014 11:22:28 GMT

I tried this one as well, but again it is showing Null().

Any solution?

Regards,

Balraj