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

[maahivee]

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

[sunny_talwar]

Check this out:

Canonical Date

[maahivee]

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.

[sunny_talwar]

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

[maahivee]

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..

[Anonymous]

Hi

EDITED :

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

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

[maahivee]

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..