topic Re: Merging two dates fields into one in QlikView

Merging two dates fields into one

sarfaraz_sheikh — Thu, 03 Sep 2015 06:02:39 GMT

Dear All,

How i can build the logic for below SQL query in QlikView

Select A.LeadNo,RoleName, SalesCloseddate as LeadGeneratedDate

from STP_LMS_LEAD A inner join STP_LMS_ROLES

Union ALL

Select A.LeadNo,RoleName,LeadGeneratedDate as LeadGeneratedDate

from STP_LMS_LEAD A inner join STP_LMS_ROLES

group by LeadGeneratedDate,RoleName

Note in above query there is two different dates field ( Sales_Closed_Date ) and (LeadGeneratedDate) and both treated as LeadGeneratedDate ...can i build this logic in Qlikview

If yes then help with simplest solution ...and help would be much appreciated..

gwassenaar‌jagan‌ please comment on this need your help

Sarfaraz

Re: Merging two dates fields into one

tresB — Thu, 03 Sep 2015 06:13:40 GMT

In QlikView you can try like:

Table:

Load A, B, Date1 as Date From <>;

Load A, B, Date2 as Date From <>;

With all fields common the two tables will get concatenated to produce a single table.

Re: Merging two dates fields into one

jagan — Thu, 03 Sep 2015 06:18:33 GMT

Hi,

Try like this

Data:

Select A.LeadNo,RoleName,Convert(DateTime,Convert(Varchar(10),SalesCloseddate,103),103) as LeadGeneratedDate,

'SalesClosed' AS DataType

from STP_LMS_LEAD(NOLOCK) A inner join STP_LMS_ROLES;

Concatenate(Data)

Select A.LeadNo,RoleName,Convert(DateTime,Convert(Varchar(10),LeadGeneratedDate,103),103) as LeadGeneratedDate,

'LeadGenerated' AS DataType

from STP_LMS_LEAD(NOLOCK) A inner join STP_LMS_ROLES

group by LeadGeneratedDate,RoleName;

Hope this helps you.

Regards,

Jagan.

Re: Merging two dates fields into one

sarfaraz_sheikh — Thu, 03 Sep 2015 06:42:40 GMT

Dear Jagan,

Is there any possibilities to do this on chart level as my table contains much more fields..

Sarfaraz

Re: Merging two dates fields into one

jagan — Thu, 03 Sep 2015 06:45:33 GMT

Hi,

Without seeing your data model and the data how can I suggest, attach some sample data and your expected results then it would be easier to understand and provide the solution.

Also, if you do this at script level it is very easier to deal in front end.

Regards,

Jagan.

Re: Merging two dates fields into one

hic — Thu, 03 Sep 2015 07:34:19 GMT

It sounds to me as if Sales_Closed_Date and LeadGeneratedDate are two very different things, and that they should not be merged into one field. But you can still solve the problem by creating a canonical date in the script.

See

Why You sometimes should Load a Master Table several times

Canonical Date

HIC

Re: Merging two dates fields into one

sarfaraz_sheikh — Thu, 03 Sep 2015 07:58:35 GMT

Dear Henric,

Can you keep any example here for my understanding related with canonical date based on below script;

Data:

LOAD

Status,

RoleName,

status_id,

LeadGeneratedDate,

LeadGeneratedDate as Main_Date

Sales_Closed_date,

FROM

[\\10.62.211.141\hdfc_qlikview\DATA\RAW_QVD\IDST_LMS_LEAD.qvd]

(qvd);

concatenate (Data)

LOAD

Status,

RoleName,

status_id,

LeadGeneratedDate,

Sales_Closed_date as Main_Date,

Sales_Closed_date,

FROM

[\\10.62.211.141\hdfc_qlikview\DATA\RAW_QVD\IDST_LMS_LEAD.qvd]

(qvd);

Sarfaraz

Re: Merging two dates fields into one

hic — Thu, 03 Sep 2015 09:02:34 GMT

Basically you are creating a canonical date in your Main_Date, but you duplicate data by loading the table twice. This will become a problem if you do a count or Sum on a field.

Instead you could do the following:

Data:

LOAD

RecNo() as PrimaryKey,

Status,

RoleName,

status_id,

LeadGeneratedDate,

Sales_Closed_date

FROM ...

Bridge:

Load PrimaryKey, LeadGeneratedDate as Date, 'Generated' as Type Resident Data;

Load PrimaryKey, Sales_Closed_date as Date, 'Closed' as Type Resident Data;

HIC