Mapping Load

[dunnalahk123]

Hi,

Can some one help me here for mapping load and apply map

i am writing script as below,

MAP:

mapping LOAD

[PSA Project pse Project ID],
[HOLD Closure date2]  
 
FROM
[$(QVD_Path)\GSD_HOLDClosuredate2.qvd]
(qvd);

HOLD:

LOAD

     [PSA Project pse Project ID],
     [HOLD Closure date],
    Applymap('MAP', [PSA Project pse Project ID],[HOLD Closure date2]) as T

FROM
[$(QVD_Path)\GSD_HOLDClosuredate.qvd]
(qvd);

out put i got as below,

but here one Project ID  can have multiple dates, but when i wrote apply map for "T" column  2 rows dates are coming same which it should not be. it should come one date as 08/16/2017 and second date as 09/29/2017. as shown in second snapshot.

can some one help me here.

[dunnalahk123]

Hi Sunny,

For Project ID  PR-539478  have 2 Date instances in GSD_HOLDClosuredate.qvd

and Project ID  PR-539478  have 2 Date instances in GSD_HOLDClosuredate2.qvd

one project id can have multiple date fields

like from above example  i am taking HOLD Closure Date  where old value text =Hold.

                                                               HOLD Closure Date 2  where New Value Text =HOLD

Date Fields is History Time stamp in above snapshot.

basically i want  to calculate days  of  old value text =Hold Time stamp - New Value Text =HOLD Time stamp.

for that to calculate in days  i need it in 2 rows  instead of 4 rows.

[sunny_talwar]

Can you share the script used to create the two qvds?

[dunnalahk123]

Hi Sunny,

GSDHOLDClosuredate:

LOAD
    
    PSA_Project_Internal_Id as [Project ID],
   

    (Date(Floor( [Created_Timestamp]),'MM/DD/YYYY')) as [HOLD Closure date],
   
    '' as  [HOLD Closure date2]

   
//    Field_Text,
//    Old_Value_Text,
//    New_Value_Text

FROM
[$(ITG_QVD_Path)\GSD_PSA_Project_History.qvd]
(qvd)
where (Field_Text= 'pse__Project_Status__c' AND  (Old_Value_Text='On Hold'))

;

INNER JOIN

LOAD
    
     [PSA_Project_Internal_Id] as [Project ID],
     [PSA Project pse Project ID]

          
       
FROM
[$(ITG_QVD_Path)\GSD_Project_Object.qvd]
(qvd)

where

 
Date(Floor( [PSA Project Created Timestamp]),'MM/DD/YYYY')>'11/01/2016'
AND  [PSA Project pse Project Status Text] <> '#'

;

STORE GSDHOLDClosuredate into [$(QVD_Path)\GSD_HOLDClosuredate.qvd];

Drop table GSDHOLDClosuredate;

GSDHOLDClosuredate2:

LOAD
    
    PSA_Project_Internal_Id as [Project ID],
   

    '' as  [HOLD Closure date],
   

    (Date(Floor( [Created_Timestamp]),'MM/DD/YYYY')) as [HOLD Closure date2]
//   
//    Field_Text,
//    Old_Value_Text,
//    New_Value_Text

FROM
[$(ITG_QVD_Path)\GSD_PSA_Project_History.qvd]
(qvd)
where (Field_Text= 'pse__Project_Status__c' AND  (New_Value_Text='On Hold'))

;

INNER JOIN

LOAD
    
     [PSA_Project_Internal_Id] as [Project ID],
     [PSA Project pse Project ID]

          
       
FROM
[$(ITG_QVD_Path)\GSD_Project_Object.qvd]
(qvd)

where

 
Date(Floor( [PSA Project Created Timestamp]),'MM/DD/YYYY')>'11/01/2016'
AND  [PSA Project pse Project Status Text] <> '#'

;

STORE GSDHOLDClosuredate2 into [$(QVD_Path)\GSD_HOLDClosuredate2.qvd];

Drop table GSDHOLDClosuredate2;

[sunny_talwar]

Could the inner join be causing multiplication of data in your qvd file?

Can you run the INNER JOIN like this and see if duplication is avoided?

INNER JOIN

LOAD DISTINCT
   
    [PSA_Project_Internal_Id] as [Project ID],
    [PSA Project pse Project ID]

         
       
FROM
[$(ITG_QVD_Path)\GSD_Project_Object.qvd]
(qvd)

where

 
Date(Floor( [PSA Project Created Timestamp]),'MM/DD/YYYY')>'11/01/2016'
AND  [PSA Project pse Project Status Text] <> '#'

;

[dunnalahk123]

Hi Sunny,

No luck , still its same.

Basically below logic gives distinct values only with out Distinct also.

LOAD
   
    [PSA_Project_Internal_Id] as [Project ID],
    [PSA Project pse Project ID]

         
       
FROM
[$(ITG_QVD_Path)\GSD_Project_Object.qvd]
(qvd)

where

 
Date(Floor( [PSA Project Created Timestamp]),'MM/DD/YYYY')>'11/01/2016'
AND  [PSA Project pse Project Status Text] <> '#'

>>>>>>>>>>>>>>>>>>>>>>>>>>> But in History object  one project has 4 Time stamps

2 time stamps with Old value Text=HOLD

2 time stamps with New value Text =HOLD

[sunny_talwar]

Would it be possible for you to share your GSD_HOLDClosuredate.qvd?

[dunnalahk123]

Hi Sunny,

I attached in the original Post.

[sunny_talwar · Accepted solution]

I think I might know what went wrong... can you try this

MAP:

MAPPING

LOAD [PSA Project pse Project ID] & AutoNumber(RowNo(), [PSA Project pse Project ID] & 1),

    [HOLD Closure date2]
FROM [$(QVD_Path)\GSD_HOLDClosuredate2.qvd] (qvd);

HOLD:

LOAD [PSA Project pse Project ID],
    [HOLD Closure date],
    Applymap('MAP', [PSA Project pse Project ID] & AutoNumber(RowNo(), [PSA Project pse Project ID] & 2)) as T

FROM [$(QVD_Path)\GSD_HOLDClosuredate.qvd] (qvd);

[sunny_talwar]

This seems to work

MAP:

MAPPING

LOAD [PSA Project pse Project ID] & AutoNumber(RowNo(), [PSA Project pse Project ID] & 1),

[HOLD Closure date2]

FROM [..\..\Downloads\GSD_HOLDClosuredate2.qvd] (qvd);

HOLD:

LOAD [PSA Project pse Project ID],

[HOLD Closure date],

Applymap('MAP', [PSA Project pse Project ID] & AutoNumber(RowNo(), [PSA Project pse Project ID] & 2), Null()) as T

FROM [..\..\Downloads\GSD_HOLDClosuredate.qvd] (qvd);

But make sure to sort the data in ascending or descending order before you create the qvds

[dunnalahk123]

HI Sunny,

Yes it works. only one thing i noticed is dates are coming in not order.

But I guess it Ok bec i need the final output in days. which is matching .

!