Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

15 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi Anil,

Check the attached file.

Hope this helps you.

regards,

Jagan.

Not applicable
Author

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????

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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