topic Re: Cross Table in QlikView

Cross Table

upaliwije — Wed, 08 Oct 2014 06:58:47 GMT

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

Re: Cross Table

geert_gelade — Wed, 08 Oct 2014 07:00:23 GMT

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

Re: Cross Table

alexandros17 — Wed, 08 Oct 2014 07:00:23 GMT

Load again the tables and concatenate them

Re: Cross Table

upaliwije — Wed, 08 Oct 2014 07:11:52 GMT

Thanks

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

Re: Cross Table

upaliwije — Wed, 08 Oct 2014 08:13:44 GMT

CAN U ELABORATE A LITTLE MORE PLS

Re: Cross Table

Sokkorn — Wed, 08 Oct 2014 08:17:19 GMT

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

Re: Cross Table

DavidFoster1 — Wed, 08 Oct 2014 08:20:59 GMT

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

Re: Cross Table

PrashantSangle — Wed, 08 Oct 2014 08:21:37 GMT

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

Re: Cross Table

upaliwije — Wed, 08 Oct 2014 08:23:26 GMT

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

Re: Cross Table

Wed, 08 Oct 2014 08:29:35 GMT

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;

Re: Re: Cross Table

Sokkorn — Wed, 08 Oct 2014 08:31:38 GMT

Hi Upali,

I got your point. Let try this script:

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]:
LOAD * Resident [tmpPROFIT];
Concatenate([FinalTable])
LOAD * Resident [tmpBUDGET];

DROP Tables [tmpPROFIT],[tmpBUDGET];

Regards,

Sokkorn

Re: Cross Table

upaliwije — Wed, 08 Oct 2014 08:32:06 GMT

Tks Max

Then I get the following Syn Table Message

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

ACTUAL << ACTUAL 1,080 lines fetched

BUDGET << BUDGET 702 lines fetched

DROP TABLES ACTUAL,BUDGET

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

Re: Cross Table

upaliwije — Wed, 08 Oct 2014 08:37:27 GMT

Still I have this Syn Table Problem with Following Message

DROP TABLES ACTUAL,BUDGET

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

Re: Re: Cross Table

Sokkorn — Wed, 08 Oct 2014 08:44:06 GMT

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

Re: Cross Table

upaliwije — Wed, 08 Oct 2014 08:53:54 GMT

Thanks All