Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Try this script and add Quarter and Month columns as per your requirement below.
Temp:
LOAD I_TCKT,
C_TCKT_STAT,
T_STMP_PROB_OCCRD,
T_STMP_PROB_RSLVD AS ReportDate,
I_APPL
FROM
TTTS.xlsx
(ooxml, embedded labels, table is Sheet1);
CONCATENATE
LOAD I_ECC,
C_ECC_EVENT_TYP,
D_INSTL AS ReportDate,
I_APPL
FROM
ECC.xlsx
(ooxml, embedded labels, table is Sheet1);
CONCATENATE
LOAD I_IPR_REF,
I_IPR_STEP,
D_STEP_END AS ReportDate,
I_APPL
FROM
IPR.xlsx
(ooxml, embedded labels, table is Sheet1);
Data:
LOAD
*,
MonthName(ReportDate) AS CalendarMonthAndYear,
Date(ReportDate) AS CalendarDate,
Year(ReportDate) AS Year
RESIDENT Temp
ORDER BY ReportDate;
DROP TABLE Temp;
Hope this helps you.
Regards,
Jagan.
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;
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
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.
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
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.
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;
hI,
Check whether this table QVTTTSTICKET exists, if exists check whether the column exists with the same case.
Regards,
Jagan.
Hi,
Do not use Date as a Alias. Its a keyword. That can be the reason.
thanks,
Rajesh Vaswani
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