Script to transforme table and manage historisation

[DAUPHINROSE]

I have an excel file to integrate into Qs and Transform.(one sheet in excel)
I must add the "date and time" of the day of integration in QS.(to each row)
Then I have 3 fields to put under the same field "Carrier".
Similarly I have 3 fields Year 1, Year 2, Year 3 to put under a WKYear Field,
 In the same way I have 3 fields Month 1 (related to Year 1), Month 2 (linked to Year 2), 
Month 3 (related to Year 3), to put under WKMonth
And I have 3 fields Workload 1 (related to Year 1 and Month 1), 
Workload 2 (related to Year 2 and Month 2), 
Workload 3 (linked to Year 3 and Month 3), to put under WKload
After as Everyday there will be an update of the file after transformation and 
it is necessary to Historise (concatenate) and also to check that the file are not 
loaded 2 times by mistake .. That's it! I really need some support!
 
So initially I have something like:

Code /Title/ Creation Date/ CA1/ CA2 /CA3 / Planned Delivery Date/Effective Delivery Date/ Year 1 Month 1 Workload 1 Year 2 Month 2 Workload 2 Year 3 Month 3 Workload 3

AND at the end the QVD stored should be structured as..:
 Integration Date and Time / Code/ Title/ Creation Date/ CARRIER /Effective Delivery Date/ WKYear/ WKMonth /WKLoad
AND also think about the historization and its controls ....because every day the excel file is loaded and concateneted to Last day QVD
Can you give me a script version and I can work on it.

Regards

[wade12]

TableName:

Load

Today() and/or Now() as IntegrationDateTime,

Code,

Title,

etc.

if (CA1 is not null, CA1, if(CA2 is not null, CA2, CA3)) as Carrier

FROM Datasource;

do similar for year/month/workload.

[DAUPHINROSE]

Hi 

thanks for the Now() function. it works.

But for the "merge" of the CARRIER and Year Month and work load no but I understand That I have to clarify my need.

As source I have

ID /Title/ CA1/CA2/Year1/Month1/Workload1/Year2/Month2/Workload2

The Target is to have

ID/Title/CARRIER/YEAR/MONTH/WORKLOAD

This means Les columns BUT creation of  additionnal Rows when I have none null values in Year/month1 and 2..

Is it more easier to understand. (if not I will send an excel example)

Thanks for your help

[DAUPHINROSE]

Dear QS users,

I have  simplify my request and splited.

I have this excel file structure at the origine:

And I woulf like to have this Next File (QVD ) structure with first the Loading date with the Now() function and consolidation of the Years, Month and workload fielsd (after i will see for the CA..consolidation).

What do I Have to write into my script to get this Transformed QVD (first step)? 

Thanks for your help.

[premvihari]

could you please try order by WKLOAD,WKYear etc..

[DAUPHINROSE]

Thks to detail please because I'm a beginer.

Regards