Store Date field from multiple tables

[tamilarasu]

Hi,

I have four tables in load script and each table linked with common field %date. I want to store this field in a table, so that I can create a master calendar, My source looks like below.

T1:

Load %date,

CustData1

From

Data1;

T2:

Load %date,

CustData2

From

Data2;

T3:

Load %date,

CustData3

From

Data3;

Can anyone suggest me a load script for this.

Thank you.

[Anonymous]

Calendar:

noconcatenate Load

%date,

year(%date) as Year,

..

resident T1:

concatenate Load

%date,

year(%date) as Year,

..

resident T2:

concatenate Load

%date,

year(%date) as Year,

..

resident T3:

[giakoum]

Hi.

you need to take all distinct %date values from all tables into a new table.

Then take min and max of %date from that table

and then based on min max create a master calendar

check this :

The Fastest Dynamic Calendar Script (Ever)

[rubenmarin1]

Hi Tamil, to create the master calendar you'll need first of all the min and max dates, you can obtain them using:

MaxMinDates:

LOAD Max(Datetmp) as MaxDate,

    Min(Datetmp) as MinDate;

LOAD FieldValue('%date', IterNo()) as Datetmp

AutoGenerate 1

While IterNo()<=FieldValueCount('%date');

LET vMinDate=FieldValue('MinDate', 1);

LET vMaxDate=FieldValue('MaxDate', 1);

DROP Table MaxMinDates;

[Gabriel]

Hi,

So you have a common field called %date which you want to store (I believe as QVD) and create a master calendar from it?

Well if that's what you want to achieve here's how to store the field

STORE %date FROM T1 INTO Date.qvd(qvd)

You'll have to create 3 ( in your case from the number of table in your example) and then concatenate all the date qvd file into 1 table (if you want store it as qvd again) to facilitate creation of master calendar.

But there might be a simpler way, which might just be to use the calendar suggested above and link your %date to it.

[tamilarasu]

Hi Ruben,

I have tried your script but it showing "MaxMinDates << AUTOGENERATE(1) 0 lines fetched". I guess first we need to store all the %date' field values from all the tables to separate tables. Can you tell me how to do that.?

[tamilarasu]

I got field no found error. Any reason?

[Anonymous]

can you post an image of your data model?

and post your script also please

[tamilarasu]

Above one is sample one. Please see the original script and attached data model screenshot.

QUALIFY '*';

UNQUALIFY '%*';

//*********************************************************

Prod_Details:

SQL SELECT *,

    CmpDt as [%Date],

    Day(CmpDt) as [Day],

    Month(CmpDt) as [Month],

    Year(CmpDt) as [Year]

FROM ProductivityDetails;

//*********************************************************

Daily_Details:

SQL SELECT *,

    CompletedDateTime as [%Date],

    Day(CompletedDateTime) as [Day],

    Month(CompletedDateTime) as [Month],

    Year(CompletedDateTime) as [Year]

FROM DailyDetails;

//*********************************************************

Quality_Details:

SQL SELECT *,

    MailCompDate as [%Date],

    Day(MailCompDate) as [Day],

    Month(MailCompDate) as [Month],

    Year(MailCompDate) as [Year]

FROM QualityDetails;

//*********************************************************

Quality_Report_Details:

SQL SELECT *,

    MailCompDate as [%Date],

    Day(MailCompDate) as [Day],

    Month(MailCompDate) as [Month],

    Year(MailCompDate) as [Year]

FROM QualityReportDetails;

[tamilarasu]

Well, Good idea. I'll give a try and let you know the result. I am waiting for Rudolf's answer.