topic Re: Store Date field from multiple tables in QlikView

Store Date field from multiple tables

tamilarasu — Thu, 16 Apr 2015 09:28:21 GMT

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.

Re: Store Date field from multiple tables

Anonymous — Thu, 16 Apr 2015 09:32:46 GMT

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:

Re: Store Date field from multiple tables

giakoum — Thu, 16 Apr 2015 09:34:16 GMT

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)

Re: Store Date field from multiple tables

rubenmarin — Thu, 16 Apr 2015 09:44:04 GMT

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;

Re: Store Date field from multiple tables

Gabriel — Thu, 16 Apr 2015 09:55:42 GMT

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.

Re: Store Date field from multiple tables

tamilarasu — Thu, 16 Apr 2015 09:58:45 GMT

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

Re: Store Date field from multiple tables

tamilarasu — Thu, 16 Apr 2015 09:59:31 GMT

I got field no found error. Any reason?

Re: Store Date field from multiple tables

Anonymous — Thu, 16 Apr 2015 10:04:08 GMT

can you post an image of your data model?

and post your script also please

Re: Store Date field from multiple tables

tamilarasu — Thu, 16 Apr 2015 10:14:32 GMT

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;

Re: Store Date field from multiple tables

tamilarasu — Thu, 16 Apr 2015 10:16:19 GMT

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

Re: Store Date field from multiple tables

rubenmarin — Thu, 16 Apr 2015 10:27:47 GMT

Hi Tamil, if the field is called %date in all tables, the FieldValue() and FieldValueCount() functions will count the values in all the tables.

As I saw in your recently posted script the name is %Date (with uppercase 'D'), change '%date' to your field name.