Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

How to join two crosstab tables?

Hi Experts,

I have created 3 crosstab tables in my app. Now i want to join these tables but when  i am trying this with concatenate function but its not working...

Pls help me

Thanks in Advance!!

1 Solution

Accepted Solutions
Highlighted
Not applicable

Hi Neha,

Has Bill said its better to take resident and concatenate to your target table and drop them.

may be like the below script..

 

Fault:

CrossTable(F1, Data1,3)
LOAD CLUSTERNO as F_Cluster1,
INSTANCE as F_INSTANCE1,
"EE_FLIGHTCNT" AS F_FlightCnt,
ALT ,
MN ,
IAS;
SQL SELECT *
FROM "DPHM_V1".FAULT;

Temp_Fault:
CrossTable(F2, Data2,3)
LOAD CLUSTERNO as F_Cluster2,
INSTANCE as F_INSTANCE2,
N1IN,
N2IN ,
P3IN;
SQL SELECT *
FROM "DPHM_V1".FAULT;

NoConcatenate
Fault1:
Concatenate(Fault)
load *
Resident Temp_Fault;

drop table Temp_Fault;

Temp_Fault3:
CrossTable(F3, Data3,3)
LOAD CLUSTERNO as F_Cluster3,
INSTANCE as F_INSTANCE3,
ENGVIB ,
MOTIN ,
MOPIN ;
SQL SELECT *
FROM "DPHM_V1".FAULT;

NoConcatenate
Fault3:
Concatenate(Fault)
load *
Resident Temp_Fault3;

drop table Temp_Fault3;

View solution in original post

11 Replies
Highlighted
Not applicable

Hi,

     Can you please attach your sample data. It will be easy to understand the problem.

Thanks,

Ashutosh

Highlighted
Not applicable

Please attach your sample script

Highlighted
Contributor III
Contributor III

Hi,

You can try to transpose the crosstab table first and then only use the concatenate function.

Highlighted
MVP
MVP

Difficult to understand without knowing your data or script.

need to either use concatenate or join function if necessary but accurate answer can be given only after knowing your data.

Highlighted
Creator II
Creator II

Thanks for the reply!

Here is my script:

Fault:
CrossTable(F1, Data1,3)
LOAD CLUSTERNO as F_Cluster1,
  INSTANCE as F_INSTANCE1,
"EE_FLIGHTCNT" AS F_FlightCnt,
    ALT ,
    MN ,
    IAS;
SQL SELECT *
FROM "DPHM_V1".FAULT;

Concatenate(Fault)
CrossTable(F2, Data2,3)
LOAD CLUSTERNO as F_Cluster2,
  INSTANCE as F_INSTANCE2,
    N1IN,
    N2IN ,
    P3IN;
    SQL SELECT *
FROM "DPHM_V1".FAULT;

I am getting error - Illegal combination of prefixes.

Highlighted
Not applicable

Hi Neha,

chk the below script..hope t helps..

 

Fault:

CrossTable(F1, Data1,3)
LOAD CLUSTERNO as F_Cluster1,
INSTANCE as F_INSTANCE1,
"EE_FLIGHTCNT" AS F_FlightCnt,
ALT ,
MN ,
IAS;
SQL SELECT *
FROM "DPHM_V1".FAULT;

Temp_Fault:
CrossTable(F2, Data2,3)
LOAD CLUSTERNO as F_Cluster2,
INSTANCE as F_INSTANCE2,
N1IN,
N2IN ,
P3IN;
SQL SELECT *
FROM "DPHM_V1".FAULT;

NoConcatenate
Fault1:
Concatenate(Fault)
load *
Resident Temp_Fault;
drop table Temp_Fault;


Regards

kumar

Highlighted
Creator II
Creator II

Hi Kumar Raman,

Thanks for your help. Its working fine.

But what would be the changes in script if i have more than 2 tables for concatenate.

Fault:
CrossTable(F1, Data1,3)
LOAD CLUSTERNO as F_Cluster1,
  INSTANCE as F_INSTANCE1,
"EE_FLIGHTCNT" AS F_FlightCnt,
ALT ,
    MN ,
    IAS;
SQL SELECT *
FROM "DPHM_V1".FAULT;

Concatenate(Fault)
Fault2:
CrossTable(F2, Data2,3)
LOAD CLUSTERNO as F_Cluster2,
  INSTANCE as F_INSTANCE2,

N1IN,
    N2IN ,
    P3IN;
    SQL SELECT *
FROM "DPHM_V1".FAULT;

Fault3:
CrossTable(F3, Data3,3)
LOAD CLUSTERNO as F_Cluster3,
INSTANCE as F_INSTANCE3,
ENGVIB ,
MOTIN ,
    MOPIN ;
     SQL SELECT *
FROM "DPHM_V1".FAULT;

My actual script is having more than 2 tables. 

Highlighted

Neha

How about CrossTable 'ing all your tables individually first with no concatenates,

Then concatenating the resultant tables from resident into your target table name.

Then dropping the temporary CrossTable 'd tables.

Best Regards,     Bill

Highlighted
Not applicable

Hi Neha,

Has Bill said its better to take resident and concatenate to your target table and drop them.

may be like the below script..

 

Fault:

CrossTable(F1, Data1,3)
LOAD CLUSTERNO as F_Cluster1,
INSTANCE as F_INSTANCE1,
"EE_FLIGHTCNT" AS F_FlightCnt,
ALT ,
MN ,
IAS;
SQL SELECT *
FROM "DPHM_V1".FAULT;

Temp_Fault:
CrossTable(F2, Data2,3)
LOAD CLUSTERNO as F_Cluster2,
INSTANCE as F_INSTANCE2,
N1IN,
N2IN ,
P3IN;
SQL SELECT *
FROM "DPHM_V1".FAULT;

NoConcatenate
Fault1:
Concatenate(Fault)
load *
Resident Temp_Fault;

drop table Temp_Fault;

Temp_Fault3:
CrossTable(F3, Data3,3)
LOAD CLUSTERNO as F_Cluster3,
INSTANCE as F_INSTANCE3,
ENGVIB ,
MOTIN ,
MOPIN ;
SQL SELECT *
FROM "DPHM_V1".FAULT;

NoConcatenate
Fault3:
Concatenate(Fault)
load *
Resident Temp_Fault3;

drop table Temp_Fault3;

View solution in original post