Concantenating of different fields from diffrenet tables into one field

[#1]

Hi,

        I have 4 different  tables from same database from  those tables i have different date fileds like below.......

      T_STMP_PROB_RSLVD----Tbale1

      D_STEP_END----Table2

      D_INSTL---Table3

      D_SERV_CREATE ---Table4

   I want to concantenate these 4 columns to one separate column as Month and Year. Is this possible in QV if yes please help how ca we do that...

Regards,

Anil

[vijay_iitkgp]

Hi,

you can use:

Table:

Load

T_STMP_PROB_RSLVD AS Date

Resident Table1;

Concatenate

Load

T_STMP_PROB_RSLVD AS Date

Resident Table2;

Concatenate

Load

T_STMP_PROB_RSLVD AS Date

Resident Table3;

Concatenate

Load

T_STMP_PROB_RSLVD AS Date

Resident Table1;

[CELAMBARASAN]

Hi,

     Load all four tables then try with this:

     TempTable:

     Load Distinct

               T_STMP_PROB_RSLVD as Date

     Resident Table1;

     Concatenate

     Load Distinct

               D_STEP_END as Date

     Resident Table2;

     Concatenate

     Load Distinct

               D_INSTL as Date

     Resident Table3;

     Concatenate

     Load Distinct

               D_SERV_CREATE as Date

     Resident Table4;

     DateTable:

     Load Distinct

               Date

     Resident

          TempTable:

     Drop Table TempTable;

Hope it helps

Celambarasan

[jagan]

Hi,

Try this

Temp:

SQL SELECT

T_STMP_PROB_RSLVD AS Date

FROM  Table1;

Concatenate

SQL SELECT

D_STEP_END AS Date

FROM Table2;

Concatenate

SQL SELECT

D_INSTL AS Date

FROM Table3;

Concatenate

SQL SELECT

D_SERV_CREATE AS Date

FROM Table4;

Table:

LOAD

     Date,

     Month(Date) AS Month,

     Year(Date) AS Year

RESIDENT Temp;

DROP TABLE Temp;

Hope this helps you.

Regards,

jagan.

[#5]

thanx for your replies i tried above methods got the corect result from 3 methods.......

and in  chart i am using below expression to get the last 12 months count

(Count({1<D_INSTL={">=$(=addmonths(monthend(max(D_INSTL))+1,-12)) <=$(=Max(D_INSTL))"}>}I_ECC))

this expression is giving correct result and another chart am using if condition with same above expression like below

=if((C_TCKT_STAT ='C'),(Count({1<T_STMP_PROB_RSLVD={">=$(=addmonths(monthend(max(T_STMP_PROB_RSLVD))+1,-12)) <=$(=Max(T_STMP_PROB_RSLVD))"}>}I_TCKT)))

for this expression am getting last 14 months count what is wrong here?????

Do we have any other way to get the last 12 months count with out using set analysis

Regards,

Anil

[jagan]

Hi,

May be for the first expression you have the data for the selected date.  But there is no data for the for the first two months in the second expression.  Check the data.

Regards,

Jagan.

[#7]

Hi Jagan i didnt understand properly what you are saying as per my understanding from your post  data is present in all the months is that condition is correct??

From the previous post i have concated 4 fields in to one field as Date, but i trying to join with this newly cretaed table to exsting tables but there i was getting below error

Field not found - <T_STMP_PROB_RSLVD>

Table:

Load

T_STMP_PROB_RSLVD AS Date

Resident QVTTTSTICKET

small example:\

Directory;

LOAD I_TCKT,

     C_TCKT_STAT,

     T_STMP_PROB_OCCRD,

     T_STMP_PROB_RSLVD as Date,

     N_SYS_APPL_AFF_SUB,

     I_APPL

FROM

D:\QVTTTSTICKET.qvd like this i have other 3 tables

Table:

Load

T_STMP_PROB_RSLVD AS Date

Resident Table1;

Concatenate

Load

D_STEP_END AS Date

Resident Table2;

Concatenate

Load

D_INSTL AS Date

Resident Tabel3;

Concatenate

Load

(D_SERV_CREATE) AS Date

Resident Table4;

[jagan]

hI,

Check whether this table QVTTTSTICKET exists, if exists check whether the column exists with the same case.

Regards,

Jagan.

[rajeshvaswani77]

Hi,

Do not use Date as a Alias. Its a keyword. That can be the reason.

thanks,

Rajesh Vaswani

[#10]

Hi jagan,

            Actually i have requirement that i have to show last 12 months records in bar charts as well as in a straight table but am not getting the correct count in straight table. To get the month name  for staright table i have created a calender, may be here the problem with the month name only.

                                                                            I have attached a sample file also with this post, please look into that

Regards,

Anil