Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

maahivee
New Contributor III

Dates to be merged to be into a single date in the same table..

Hello Guys,

I have a requirement where I have to merge two date fields date1 and date2 which are in the same table.

So I have tried doing it like this in the script.....

table1:

Select date1 as enddate

from table;

outer join (table1)

Select date2  as enddate

from table;

Which is constantly giving ODBC Failed error.. What I am trying to achieve here is a common week column out of this merged date and show sum(sales) based on that common week dimension.. Also this table has around 5M records.

OR I should say in a straight table the dimension be week and the expression1 be Sum(sales) based on date1 and Sum(sales) based on date2....   

I do have a master calendar in my data model and I am grabbing the week dimension from it.


Is there any other way doing it without changing the data model, in the script or in the straight table??

Thanks in advance

Tags (1)
6 Replies

Re: Dates to be merged to be into a single date in the same table..

Check this out:

Canonical Date

maahivee
New Contributor III

Re: Dates to be merged to be into a single date in the same table..

Hello Sunny - I tried doing the canonical date and it is not working may be i am doing something wrong in the applymap() script..

Can you please elaborate the script for me a bit, I have date1 and date2 in the same table orders.

Re: Dates to be merged to be into a single date in the same table..

Would you be able to share the script you have tried thus far?

maahivee
New Contributor III

Re: Dates to be merged to be into a single date in the same table..

Firstly I do not have fields like OrderID and OrderlineID....

I dont think I am doing any good with Applymap() Kind of dumb here..

ItemID2TaskDate:

Mapping LOAD

%LaborKey,

[Task Transaction Date]

Resident Labor;

SOID2TaskDate:

Mapping LOAD

%LaborKey,

[Transaction End Date]

Resident Labor;

DateBridge:

     Load [Item Num], Applymap('ItemID2TaskDate',[SO Number],Null()) as CanonicalDate, 'Task' as DateType

          Resident Labor;

     Load [Item Num], Applymap('SOID2TaskDatete',[SO Number],Null()) as CanonicalDate, 'TaskEnd' as DateType

          Resident Labor;

Seriously Do not know what am I doing here.. Kind of dumb as I said..

galax_allu
Valued Contributor

Re: Dates to be merged to be into a single date in the same table..

Hi

EDITED :

Applymap('ItemID2TaskDate',[SO Number],Null()) as CanonicalDate, 'Task' as DateType




I do not think  [SO Number]  field declared in MAPPING table ?




Highlighted
maahivee
New Contributor III

Re: Dates to be merged to be into a single date in the same table..

Hello Sunny - Here is the script I am using now

Labor2TaskDate:

Mapping LOAD Distinct

%LaborKey1,

[Task Transaction Date]

Resident Labor;


Labor2TaskEndDate:

Mapping LOAD Distinct

%LaborKey1,

[Transaction End Date]

Resident Labor;

DateBridge:

     Load %LaborKey1,

          Applymap('Labor2TaskDate',%LaborKey1,Null()) as CanonicalDate,

          'Task' as DateType

          Resident Labor;

     Load %LaborKey1,

          Applymap('Labor2TaskEndDate',%LaborKey1,Null()) as CanonicalDate,

          'TaskEnd' as DateType

          Resident Labor;

  

So now after i get the canonical date i am using it in a straight table as a dimension.. "hour(canonical date)" as dimension

and i have two expressions in this table 1: Sum(Transaction Amount) for status = open

2: sum(transaction Amount) for status = completed.. basically i am trying to show all the open and closed transaction hour by hour based on canonical date, Since i cannot use 2 dates in the dimensions for one hour dimension.

The problem is not giving correct numbers in either of the feilds..

Please advise a better solution for this..