Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Cross Table

Hi Friends

PROFIT:

CrossTable(DESCRIPTION, ACTUAL, 4)

LOAD BAL_BRANCH_CODE AS BCOD,

     YEAR,

     MONTH,

     CLA_CODE,

     GROSS_PREMIUM,

     CEDED_PREMIUM,

        NET_PROFIT

FROM

Actual_TB.Qvd

(qvd)

WHERE BAL_BRANCH_CODE='1M00'

;

Concatenate(PROFIT)

CrossTable(DESCRIPTION, BUDGET, 4)

LOAD BGT_BRANCH_CODE AS BCOD,

    YEAR,

     MONTH,

     CLA_CODE,

     GROSS_PREMIUM,

     CEDED_PREMIUM,

  

     NET_PROFIT

FROM

Budget_TB.Qvd

(qvd)

WHERE BGT_BRANCH_CODE='1M00';

When I run the above script I get the following error message

llegal combination of prefixes

Concatenate(PROFIT)

CrossTable(DESCRIPTION, BUDGET, 4)

LOAD BGT_BRANCH_CODE AS BCOD,

    YEAR,

     MONTH,

     CLA_CODE,

     GROSS_PREMIUM,

     CEDED_PREMIUM,

Pls help me to solve this problem.

My requirement is to load two cross table and concatenate the same. If you find a better solution pls advice

me

1 Solution

Accepted Solutions
Master
Master

Hi Upali,

What about this way

tmpPROFIT: 

CrossTable(DESCRIPTION, ACTUAL, 4) 

LOAD BAL_BRANCH_CODE AS BCOD, 

     YEAR, 

     MONTH, 

     CLA_CODE, 

     GROSS_PREMIUM, 

     CEDED_PREMIUM, 

     NET_PROFIT 

FROM Actual_TB.Qvd (qvd) WHERE BAL_BRANCH_CODE='1M00'; 

 

tmpBUDGET: 

CrossTable(DESCRIPTION, BUDGET, 4) 

LOAD BGT_BRANCH_CODE AS BCOD, 

    YEAR, 

     MONTH, 

     CLA_CODE, 

     GROSS_PREMIUM, 

     CEDED_PREMIUM, 

     NET_PROFIT 

FROM Budget_TB.Qvd (qvd) WHERE BGT_BRANCH_CODE='1M00'; 

 

[FinalTable]:

NoConcatenate

LOAD * Resident [tmpPROFIT];

DROP Table [tmpPROFIT];

Concatenate([FinalTable]) 

LOAD * Resident [tmpBUDGET]; 

DROP Table [tmpBUDGET];

Regards,

Sokkorn

View solution in original post

14 Replies
Creator
Creator

Try to first load the tables and concatenate them. Afterwards you can do the cross table load on the resident table.

Load again the tables and concatenate them

Creator II
Creator II

Thanks

It is not clear to me .Pls elaborate possibly with syntax

Creator II
Creator II

CAN U ELABORATE A LITTLE MORE PLS

Master
Master

Hi Upali,

I saw both table have same fields. Just remove Concatenate(PROFIT) and load data again. Auto concatenate will perform while load script.

Regards,

Sokkorn

Specialist
Specialist

Hi Upali

You need to think of Qlik scripting as a procedure not a SQL query. You have written something like a UNION SQL query, but you need to write it more like a multi-step stored procedure.

One approach would be:

Step 1: Load Crosstable 1 into table A

Step 2: Load Crosstable into a temporary table B

Step 3: Concatenate the contents of resident temporary table B with table A

Step 4: Drop temporary table B.

Another approach

Step 1: Load table 1 (no cross table) into temporary table B

Step2: Concatenate table 2 (no crosstable) into temporary table B

Step3: Crosstable temporary table B into table C

Step4: Drop temporary table B

Hi,

Try this,

PROFIT1:

CrossTable(DESCRIPTION, ACTUAL, 4)

LOAD BAL_BRANCH_CODE AS BCOD,

     YEAR,

     MONTH,

     CLA_CODE,

     GROSS_PREMIUM,

     CEDED_PREMIUM,

        NET_PROFIT

FROM

Actual_TB.Qvd

(qvd)

WHERE BAL_BRANCH_CODE='1M00'

PROFIT2:

CrossTable(DESCRIPTION, BUDGET, 4)

LOAD BGT_BRANCH_CODE AS BCOD,

    YEAR,

     MONTH,

     CLA_CODE,

     GROSS_PREMIUM,

     CEDED_PREMIUM,

  

     NET_PROFIT

FROM

Budget_TB.Qvd

(qvd)

WHERE BGT_BRANCH_CODE='1M00';

Load * Resident PROFIT1

conacatenate

Load* Resident PROFIT2

drop tables PROFIT1,PROFIT2;

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Creator II
Creator II

Hi Sokkorn,

Tks

But then I get following Syn Message How can I avoid IT

Actual_TB << Actual_TB 20 lines fetched

ACTUAL << Actual_TB 540 lines fetched

Budget_TB << Budget_TB 13 lines fetched

BUDGET << Budget_TB 351 lines fetched

$Syn 1 = BCOD+YEAR+MONTH+CLA_CODE+DESCRIPTION

Not applicable

U cannot use Concatenate and CrossTable both at same time.

Best solution is rename your BUDGET field to ACTUAL and they concatenate automatically. Just need to add identifiet of source table like FileBaseName() as Source

Other way:

CONCATENATE (PROFIT) LOAD * RESIDENT Budget_TB; DROP TABLE Budget_TB;