topic Concantenating of different fields from diffrenet tables into one field in QlikView

Concantenating of different fields from diffrenet tables into one field

Tue, 21 Feb 2012 12:04:37 GMT

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

Concantenating of different fields from diffrenet tables into one field

vijay_iitkgp — Tue, 21 Feb 2012 12:09:03 GMT

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

CELAMBARASAN — Tue, 21 Feb 2012 12:11:57 GMT

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

Concantenating of different fields from diffrenet tables into one field

jagan — Tue, 21 Feb 2012 12:13:42 GMT

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.

Concantenating of different fields from diffrenet tables into one field

Tue, 21 Feb 2012 13:43:37 GMT

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

Concantenating of different fields from diffrenet tables into one field

jagan — Tue, 21 Feb 2012 14:10:46 GMT

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.

Concantenating of different fields from diffrenet tables into one field

Tue, 21 Feb 2012 14:24:17 GMT

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;

Concantenating of different fields from diffrenet tables into one field

jagan — Tue, 21 Feb 2012 14:29:01 GMT

hI,

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

Regards,

Jagan.

Concantenating of different fields from diffrenet tables into one field

rajeshvaswani77 — Tue, 21 Feb 2012 14:37:22 GMT

Hi,

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

thanks,

Rajesh Vaswani

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

Thu, 23 Feb 2012 08:00:34 GMT

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

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

jagan — Thu, 23 Feb 2012 08:24:39 GMT

Hi Anil,

Check the attached file.

Hope this helps you.

regards,

Jagan.

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

Thu, 23 Feb 2012 09:22:39 GMT

Hi Jagan ..

Thnx for reply, Now the straight table is showing correct count for last column only(IPR) but other two columns still showing wrong count as per bar chart.. please look into that straight table once again, For example in Straight table for Tickets count on Mar 2011 its showing 0 but in chart we can see 73,,

In Straight table for dimension you have used D_STEP_END this dimension is from IPR table so its its displaying correct count for that column but other two tables having different dimensions i.e T_STMP_PROB_RSLD,D_INSTL....

How can we combine these 3 Date coumns into one column i.e Monthname or whatever it is????

Concantenating of different fields from diffrenet tables into one field

jagan — Thu, 23 Feb 2012 09:42:31 GMT

Hi,

Combine all tables into single table by using Concatenate. While concatenating give the names of Date Fields as same name.

Ex:

Load

Date1 AS ReportDate,

From Source1;

Concatenate

Load

Date2 AS ReportDate,

From Source2;

;

Like this do for all 4 tables.

Now use the Date(ReportDate) field in the straight table.

Hope this helps you.

Regards,

Jagan.

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

Thu, 23 Feb 2012 09:56:05 GMT

Hi Jagan,

I was trying as per your sugesstion ,, but still no use not able get the correct count, plz find the attached sampl file

Regards,

Anil

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

jagan — Mon, 27 Feb 2012 11:27:49 GMT

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.

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

Tue, 28 Feb 2012 07:23:54 GMT

thnx jagan now its working perfectly as what i want.....