topic Re: Concatenate in QlikView

Concatenate

Wed, 11 Jul 2012 04:21:56 GMT

Trying to concatenate data from two tables.

Table1_ID	Table1_Letter
1	a
2	b
3	c

Table2_ID	Table2_Letter	Table2_Dum
4	d	aa
5	e	bb
6	f	cc
1	dd	DD

The result Im searching for is as follows

Table1_ID	Table1_Letter
1	a
2	b
3	c
4	d
5	e
6	f

This is the script I'm using. I've tried so many different versions and just cant seem to get it right. Appreciate your help.

Table1:

LOAD Table1_ID,

Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet1);

Table2Temp:

NoConcatenate

LOAD Table2_ID as Table1_ID,

Table2_Letter as Table1_Letter,

Table2_Dum

FROM

(ooxml, embedded labels, table is Sheet2);

Concatenate(Table1)

LOAD Table1_ID,

Table1_Letter

// Table2_Dum

Resident Table2Temp

where NOT EXISTS (Table1_ID);

Re: Concatenate

Anonymous — Wed, 11 Jul 2012 04:27:28 GMT

Why not do a Left join (Table1)?

That way you'll only keep the ID's that exist in table 1 if they are present in both tables.

Re: Concatenate

Wed, 11 Jul 2012 04:55:22 GMT

This is what I now have with the Left Join - still producing the same results.

Table1:

LOAD Table1_ID,

Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet1);

Table2Temp:

NoConcatenate

LOAD Table2_ID as Table1_ID,

Table2_Letter as Table1_Letter,

Table2_Dum

FROM

(ooxml, embedded labels, table is Sheet2);

TableFinal:

NoConcatenate

LOAD *

Resident Table1;

Left Join

LOAD Table1_ID,

Table1_Letter

resident Table2Temp

where NOT EXISTS (Table1_ID);

Re: Concatenate

giakoum — Wed, 11 Jul 2012 05:14:04 GMT

In your example, record

must not be in the result set. What is the logic here? You include

because it was first encuntered or... ?

Re: Concatenate

Wed, 11 Jul 2012 05:21:56 GMT

Correct

1 dd DD

must not be included.

The logic is that the data in Table1 must all be included plus the data in Table2 where the Table2_ID doesnt exist in Table1.

Re: Concatenate

giakoum — Wed, 11 Jul 2012 05:29:57 GMT

Please see attached

Re: Concatenate

Wed, 11 Jul 2012 05:40:18 GMT

Thanks Ioannis - that worked for this example.

I now need to apply this to much larger tables with 30 columns each and each with different names.

Am I able to do something like this?

Table1:

LOAD Table1_ID,

Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet1);

LOad Table2_ID as Table1_ID,

Table2_Letter as Table1_Letter

where not exists (Table2_ID);

LOAD Table2_ID,

Table2_Letter

FROM

(ooxml, embedded labels, table is Sheet2);

Re: Concatenate

giakoum — Wed, 11 Jul 2012 05:42:13 GMT

If you have one key column (ID) then the logic is pretty much the same. But some sample data would help, if available.

Re: Concatenate

nagaiank — Wed, 11 Jul 2012 05:47:33 GMT

I think your where cause should be as follows:

where not exists (Table1_ID,Table2_ID)

Re: Concatenate

Anonymous — Wed, 11 Jul 2012 05:52:14 GMT

What I meant was, why not do like this?

Table1:

LOAD Table1_ID,

Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet1);

Left join (Table1)

LOAD Table2_ID as Table1_ID,

Table2_Letter as Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet2);

Re: Concatenate

nagaiank — Wed, 11 Jul 2012 05:57:08 GMT

Johannes,

Please see the post No. 4 above. In order to exclude one line from Table2, the where clause is needed.

Thanks.

Re: Concatenate

Thu, 12 Jul 2012 06:32:26 GMT

This is my actual code. Have tried many variations and am now frustrated as cannot seem to find the solution. The code runs - it just doesnt concatenate at the end.

LOADING THE INCOME TABLE

Income:

LOAD Company as BrokerCode,

Branch,

Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) &'-'& Risk &'-'& Income_Class as MapKey,

Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) &'-'& Risk &'-'& Income_Class as BudgetMapKey,

Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) as MapKeyServicer,

Servicer,

Client_Number,

Client_Name,

Income_Class,

Risk,

Premium,

Gross_Brokerage,

Sub_Agent,

Broker_Fees,

Fees,

Net_Income,

Invoice_Period,

Year(AddMonths(Invoice_Period,6)) as Fiscal_Year,

Month(Invoice_Period) as I_Month,

Cover_No,

Risk_Class

FROM

(qvd);

CREATING MAP TO INSERT NET INCOME IN BUDGET TABLE

Income_Budget_Map_Net_Income:

Mapping

Load

distinct(MapKey),

Sum([Net_Income]) as Map_Net_Income

Resident Income

Group By MapKey;

LOADING BUDGET TABLE AND APPLYING MAP

Budget:

Load

ApplyMap('Income_Budget_Map_Net_Income',BudgetMapKey,'Missing') as Budget_Net_Income;

LOAD CLB_Company,

CLB_Branch,

CLB_ClientNo,

Month(makedate(left(CLB_Period,4),right(CLB_Period,2))) as Budget_Month,

Year(AddMonths(makedate(left(CLB_Period,4),right(CLB_Period,2)),6)) as Budget_Fiscal_Year,

CLB_Company & '-' & CLB_Branch & '-' & CLB_ClientNo & '-' & Year(AddMonths(makedate(left(CLB_Period,4),right(CLB_Period,2)),6)) &'-'& Month(makedate(left(CLB_Period,4),right(CLB_Period,2))) &'-'& CLB_RiskCode &'-'& CLB_IncomeClass as BudgetMapKey,

CLB_RiskCode,

CLB_IncomeClass,

CLB_B_NET_INCOME

FROM

(qvd);

ATTEMPTING TO CONCATENATE INTO THE BUDGET TABLE THE DATA FROM THE INCOME TABLE WHERE THE “BUDGETMAPKEY” DOES NOT EXIST.

BudgetTemp1:

Load *

where not exists (BudgetMapKey);

LOAD BrokerCode as CLB_Company,

Branch as CLB_Branch,

Client_Number as CLB_ClientNo,

I_Month as Budget_Month,

Fiscal_Year as Budget_Fiscal_Year,

BudgetMapKey,

Risk as CLB_RiskCode,

Income_Class as CLB_IncomeClass,

'-' as CLB_B_NET_INCOME,

Net_Income as Budget_Net_Income

Resident Income;

Re: Concatenate

nagaiank — Thu, 12 Jul 2012 07:02:50 GMT

A modified script is given below. Please see it this works. As I do not have the qvd files, I was unable to test by actually running the script. The changes are highlighted in red.

//LOADING THE INCOME TABLE

Income:

LOAD Company as BrokerCode,

Branch,

Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) &'-'& Risk &'-'& Income_Class as MapKey,

// Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) &'-'& Risk &'-'& Income_Class as BudgetMapKey,

Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) as MapKeyServicer,

Servicer,

Client_Number,

Client_Name,

Income_Class,

Risk,

Premium,

Gross_Brokerage,

Sub_Agent,

Broker_Fees,

Fees,

Net_Income,

Invoice_Period,

Year(AddMonths(Invoice_Period,6)) as Fiscal_Year,

Month(Invoice_Period) as I_Month,

Cover_No,

Risk_Class

FROM (qvd);

//CREATING MAP TO INSERT NET INCOME IN BUDGET TABLE

Income_Budget_Map_Net_Income:

Mapping

Load

distinct(MapKey),

Sum([Net_Income]) as Map_Net_Income

Resident Income

Group By MapKey;

//LOADING BUDGET TABLE AND APPLYING MAP

Budget:

Load

ApplyMap('Income_Budget_Map_Net_Income',BudgetMapKey,'Missing') as Budget_Net_Income;

LOAD CLB_Company,

CLB_Branch,

CLB_ClientNo,

Month(makedate(left(CLB_Period,4),right(CLB_Period,2))) as Budget_Month,

Year(AddMonths(makedate(left(CLB_Period,4),right(CLB_Period,2)),6)) as Budget_Fiscal_Year,

CLB_RiskCode,

CLB_IncomeClass,

CLB_B_NET_INCOME

FROM (qvd);

//ATTEMPTING TO CONCATENATE INTO THE BUDGET TABLE THE DATA FROM THE INCOME TABLE WHERE THE “BUDGETMAPKEY” DOES NOT EXIST.

//BudgetTemp1:

//Load *

//where not exists (BudgetMapKey);

Concatenate (Budget)

LOAD BrokerCode as CLB_Company,

Branch as CLB_Branch,

Client_Number as CLB_ClientNo,

I_Month as Budget_Month,

Fiscal_Year as Budget_Fiscal_Year,

MapKey as BudgetMapKey,

Risk as CLB_RiskCode,

Income_Class as CLB_IncomeClass,

'-' as CLB_B_NET_INCOME,

Net_Income as Budget_Net_Income

Resident Income

where Not Exists(BudgetMapKey,MapKey);

Re: Concatenate

Thu, 12 Jul 2012 07:14:02 GMT

Table1:

LOAD Table1_ID,

Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet1);

Table2Temp:

NoConcatenate

LOAD Table2_ID as Table1_ID,

Table2_Letter as Table1_Letter,

Table2_Dum

FROM

(ooxml, embedded labels, table is Sheet2);

left join (Table2Temp)

load

Table1_ID,

Table1_Letter,

1 as FLG

resident Table1;

Concatenate(Table1)

LOAD Table1_ID,

Table1_Letter

// Table2_Dum

Resident Table2Temp

where isnull(FLG);