Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks
It is not clear to me .Pls elaborate possibly with syntax
CAN U ELABORATE A LITTLE MORE PLS
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
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
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
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;