Cross Table

[upaliwije]

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

[geert_gelade]

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

[alexandros17]

Load again the tables and concatenate them

[upaliwije]

Thanks

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

[upaliwije]

CAN U ELABORATE A LITTLE MORE PLS

[Sokkorn]

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

[DavidFoster1]

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

[PrashantSangle]

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

[upaliwije]

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

[#10]

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;