Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Concantenating of different fields from diffrenet tables into one field

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Concantenating of different fields from diffrenet tables into one field

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.

15 Replies
vijay_iitkgp
Valued Contributor

Concantenating of different fields from diffrenet tables into one field

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;

Concantenating of different fields from diffrenet tables into one field

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

MVP
MVP

Concantenating of different fields from diffrenet tables into one field

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.

Not applicable

Concantenating of different fields from diffrenet tables into one field

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

MVP
MVP

Concantenating of different fields from diffrenet tables into one field

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.

Not applicable

Concantenating of different fields from diffrenet tables into one field

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;

MVP
MVP

Concantenating of different fields from diffrenet tables into one field

hI,

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

Regards,

Jagan.

rajeshvaswani77
Valued Contributor III

Concantenating of different fields from diffrenet tables into one field

Hi,

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

thanks,

Rajesh Vaswani

Not applicable

Re: Concantenating of different fields from diffrenet tables into one field

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

Community Browser