Problem while concatenation - Qlik Community

shumailh · ‎2009-08-04

I am trying to concatenate two tables but unable to get all the fields except cardno for the second table in the resultant. Can any one please help.


V7:
LOAD CARD_NO,
 'C' as V7,
 LoadDate3 as LoadDate7,
 limit3 AS limit7,
 bal3 AS bal7,
 delq3 AS delq7,
 BLOCK1_MOM3 AS BLOCK1_MOM7,
 BLOCK2_MOM3 AS BLOCK2_MOM7,
 BLOCK3_MOM3 AS BLOCK3_MOM7,
 BLOCK_DATE1_MOM3 as BLOCK_DATE1_MOM7,
 BLOCK_DATE2_MOM3 as BLOCK_DATE2_MOM7,
 BLOCK_DATE3_MOM3 as BLOCK_DATE3_MOM7
RESIDENT V3;
Concatenate LOAD
 CARD_NO, 'C' as V7,
 LoadDate5 as LoadDate7,
 limit5 AS limit7,
 bal5 AS bal7,
 delq5 AS delq7,
 CARD5 as CARD7,
 BLOCK1_MOM5 AS BLOCK1_MOM7,
 BLOCK2_MOM5 AS BLOCK2_MOM7,
 BLOCK3_MOM5 AS BLOCK3_MOM7,
 BLOCK_DATE1_MOM5 as BLOCK_DATE1_MOM7,
 BLOCK_DATE2_MOM5 as BLOCK_DATE2_MOM7,
 BLOCK_DATE3_MOM5 as BLOCK_DATE3_MOM7
RESIDENT V5;

stephencredmond · ‎2009-08-04

Hi,

Have you checked to confirm that there is actually any values in the V5 table?

Other than that, can't really help without any sources.

Stephen

Report Inappropriate Content · ‎2009-08-04

In your concatenate you have this:

CARD5 as CARD7,

This doesn't stand in the first table.

So your concatenation won't work

Gr
Rey-man

Report Inappropriate Content · ‎2009-08-04

That's strange ??

I don't see any syntax problem here?? Please check your data in the second table.

Note : even if the CARD7 field isn't in the first table, It should work (as your using the CONCATENANTE instruction).

sathishkumar_go · ‎2009-08-04

Hi use drop table before concatenation.

-Sathish

shumailh · ‎2009-08-05

Hi Bertrand,

You are right there is no problem in the current query, basically i was doing aggregation in previous tables and some of the fields where considering as a string instead of numeric i.e. cardno, blockcode etc... so i use maxstring for them. Below is the working code for your reference,


V1:
LOAD
 CARD_NO, 'P' as V1,
 LoadDate as LoadDate_V1,
 CARD_NO as CARD1,
 BASE_SEGMENT_NBR AS BASE_SEGMENT_NBR1,
 MID(CARD_NO,1,17) as Mid_14_1,
 MID(CARD_NO,18,1) as Replacement1,
 LoadDate as LoadDate1,
 CREDIT_LIMIT AS limit1,
 CUR_BAL AS bal1,
 DELQ_DAYS AS delq1,
 BLOCKCODE1 AS BLOCK1_MOM1,
 BLOCKCODE2 AS BLOCK2_MOM1,
 BLOCKCODE3 AS BLOCK3_MOM1,
 BLOCK_DATE1 as BLOCK_DATE1_MOM1,
 BLOCK_DATE2 as BLOCK_DATE2_MOM1,
 BLOCK_DATE3 as BLOCK_DATE3_MOM1
RESIDENT $(CurrentFileName)
WHERE
//(
 (index('UVWDGLRFX',TRIM(BLOCKCODE1)) and num#(CUR_BAL)=0) OR
 (index('UVWDGLRFX',TRIM(BLOCKCODE2)) and num#(CUR_BAL)=0) OR
 (index('UVWDGLRFX',TRIM(BLOCKCODE3)) and num#(CUR_BAL)=0)
 /*
 (round(CUR_BAL)=0 AND Wildmatch(TRIM(BLOCKCODE1),'U','V','W','D','G','L','R','F','X')) OR
 (round(CUR_BAL)=0 AND Wildmatch(TRIM(BLOCKCODE2),'U','V','W','D','G','L','R','F','X')) OR
 (round(CUR_BAL)=0 AND Wildmatch(TRIM(BLOCKCODE3),'U','V','W','D','G','L','R','F','X'))
 */
//)
OR Wildmatch(TRIM(BLOCKCODE1),'R') OR Wildmatch(TRIM(BLOCKCODE2),'R') OR Wildmatch(TRIM(BLOCKCODE3),'R')
;


V2:
LOAD
 CARD_NO,'P' as V2,
 LoadDate as LoadDate_V2,
 CARD_NO as CARD2,
 BASE_SEGMENT_NBR AS BASE_SEGMENT_NBR2,
 MID(CARD_NO,1,17) as Mid_14_2,
 MID(CARD_NO,18,1) as Replacement2,
 LoadDate as LoadDate2,
 CREDIT_LIMIT AS limit2,
 CUR_BAL AS bal2,
 DELQ_DAYS AS delq2,
 BLOCKCODE1 AS BLOCK1_MOM2,
 BLOCKCODE2 AS BLOCK2_MOM2,
 BLOCKCODE3 AS BLOCK3_MOM2,
 BLOCK_DATE1 as BLOCK_DATE1_MOM2,
 BLOCK_DATE2 as BLOCK_DATE2_MOM2,
 BLOCK_DATE3 as BLOCK_DATE3_MOM2
RESIDENT $(CurrentFileName)
WHERE
(
 /*(index('LF', BLOCKCODE1) AND index('ACDFGILNOPQTUVWXZ ', BLOCKCODE2) AND index('ACDFGILNOPQTUVWXZ ', BLOCKCODE3)) OR
 (index('ACDFGILNOPQTUVWXZ ', BLOCKCODE1) AND index('LF', BLOCKCODE2) AND index('ACDFGILNOPQTUVWXZ ', BLOCKCODE3)) OR
 (index('ACDFGILNOPQTUVWXZ ', BLOCKCODE1) AND index('ACDFGILNOPQTUVWXZ ', BLOCKCODE2) AND index('LF', BLOCKCODE3))
 (Wildmatch(trim(BLOCKCODE1),'L','F') AND Wildmatch(trim(BLOCKCODE2), 'A','C','D','F','G','I','L','N','O','P','Q','T','U','V','W','X','Z',' ') AND WildMatch(trim(BLOCKCODE3),'A','C','D','F','G','I','L','N','O','P','Q','T','U','V','W','X','Z',' ')) OR
 (Wildmatch(trim(BLOCKCODE2), 'A','C','D','F','G','I','L','N','O','P','Q','T','U','V','W','X','Z',' ') AND Wildmatch(trim(BLOCKCODE2),'L','F') AND WildMatch(trim(BLOCKCODE3),'A','C','D','F','G','I','L','N','O','P','Q','T','U','V','W','X','Z',' ')) OR
 (Wildmatch(trim(BLOCKCODE2), 'A','C','D','F','G','I','L','N','O','P','Q','T','U','V','W','X','Z',' ') AND Wildmatch(trim(BLOCKCODE2),'A','C','D','F','G','I','L','N','O','P','Q','T','U','V','W','X','Z',' ') AND Wildmatch(trim(BLOCKCODE3),'L','F'))*/
 (Wildmatch(trim(BLOCKCODE1),'L','F') AND trim(BLOCKCODE2) <> 'R' AND trim(BLOCKCODE3) <> 'R' AND NUM#(CUR_BAL)<>0) OR
 (trim(BLOCKCODE1) <> 'R' AND Wildmatch(trim(BLOCKCODE2),'L','F') AND trim(BLOCKCODE3) <> 'R' AND NUM#(CUR_BAL)<>0) OR
 (trim(BLOCKCODE1) <> 'R' AND trim(BLOCKCODE2) <> 'R' AND Wildmatch(trim(BLOCKCODE3),'L','F') AND NUM#(CUR_BAL)<>0)
);

V3:
LOAD
 CARD_NO,'P' as V3,
 LoadDate as LoadDate_V3,
 CARD_NO as CARD3,
 BASE_SEGMENT_NBR AS BASE_SEGMENT_NBR3,
 MID(CARD_NO,1,17) as Mid_14_3,
 MID(CARD_NO,18,1) as Replacement3,
 LoadDate as LoadDate3,
 CREDIT_LIMIT AS limit3,
 CUR_BAL AS bal3,
 DELQ_DAYS AS delq3,
 BLOCKCODE1 AS BLOCK1_MOM3,
 BLOCKCODE2 AS BLOCK2_MOM3,
 BLOCKCODE3 AS BLOCK3_MOM3,
 BLOCK_DATE1 as BLOCK_DATE1_MOM3,
 BLOCK_DATE2 as BLOCK_DATE2_MOM3,
 BLOCK_DATE3 as BLOCK_DATE3_MOM3
RESIDENT $(CurrentFileName)
WHERE NOT Exists(CARD1, CARD_NO) and NOT Exists(CARD2, CARD_NO);

V6:
LOAD
 MaxString(CARD_NO) as CARD_NO,
 'P' as V6,
 Max(LoadDate_V2) as LoadDate_V6,
 MaxString(CARD2) as CARD6,
 BASE_SEGMENT_NBR2 as BASE_SEGMENT_NBR6,
 Mid_14_2 AS Mid_14_6,
 Max(Replacement2) AS Replacement6,
 Max(LoadDate2) as LoadDate6,
 Max(limit2) AS limit6,
 Max(bal2) AS bal6,
 Max(delq2) AS delq6,
 MaxString(BLOCK1_MOM2) AS BLOCK1_MOM6,
 MaxString(BLOCK2_MOM2) AS BLOCK2_MOM6,
 MaxString(BLOCK3_MOM2) AS BLOCK3_MOM6,
 Max(BLOCK_DATE1_MOM2) as BLOCK_DATE1_MOM6,
 Max(BLOCK_DATE2_MOM2) as BLOCK_DATE2_MOM6,
 Max(BLOCK_DATE3_MOM2) as BLOCK_DATE3_MOM6
RESIDENT V2
GROUP BY BASE_SEGMENT_NBR2, Mid_14_2;
LEFT JOIN (V6) LOAD
 MaxString(CARD_NO) as CARD_NO,
 'P' as V6,
 Max(LoadDate_V3) as LoadDate_V6,
 MaxString(CARD3) as CARD6,
 BASE_SEGMENT_NBR3 as BASE_SEGMENT_NBR6,
 Mid_14_3 as Mid_14_6,
 Max(Replacement3) AS Replacement6,
 Max(LoadDate3) as LoadDate6,
 Max(limit3) AS limit6,
 Max(bal3) AS bal6,
 Max(delq3) AS delq6,
 MaxString(BLOCK1_MOM3) AS BLOCK1_MOM6,
 MaxString(BLOCK2_MOM3) AS BLOCK2_MOM6,
 MaxString(BLOCK3_MOM3) AS BLOCK3_MOM6,
 Max(BLOCK_DATE1_MOM3) as BLOCK_DATE1_MOM6,
 Max(BLOCK_DATE2_MOM3) as BLOCK_DATE2_MOM6,
 Max(BLOCK_DATE3_MOM3) as BLOCK_DATE3_MOM6
RESIDENT V3 GROUP BY BASE_SEGMENT_NBR3, Mid_14_3;

V4:
LOAD
 CARD_NO,'P' as V4,
 LoadDate_V6 as LoadDate_V4,
 CARD6 as CARD4,
 BASE_SEGMENT_NBR6 AS BASE_SEGMENT_NBR4,
 Mid_14_6 as Mid_14_4,
 Replacement6 as Replacement4,
 LoadDate6 as LoadDate4,
 limit6 AS limit4,
 bal6 AS bal4,
 delq6 AS delq4,
 BLOCK1_MOM6 AS BLOCK1_MOM4,
 BLOCK2_MOM6 AS BLOCK2_MOM4,
 BLOCK3_MOM6 AS BLOCK3_MOM4,
 BLOCK_DATE1_MOM6 as BLOCK_DATE1_MOM4,
 BLOCK_DATE2_MOM6 as BLOCK_DATE2_MOM4,
 BLOCK_DATE3_MOM6 as BLOCK_DATE3_MOM4
RESIDENT V6
WHERE NOT Exists(Mid_14_3, Mid_14_6) and NOT Exists(CARD3, CARD6);

drop table V6;


V5:
LOAD CARD_NO,
 'P' as V5,
 LoadDate_V4 as LoadDate_V5,
 CARD4 as CARD5,
 BASE_SEGMENT_NBR4 as BASE_SEGMENT_NBR5,
 Mid_14_4 AS Mid_14_5,
 Replacement4 AS Replacement5,
 LoadDate4 as LoadDate5,
 limit4 AS limit5,
 bal4 AS bal5,
 delq4 AS delq5,
 BLOCK1_MOM4 AS BLOCK1_MOM5,
 BLOCK2_MOM4 AS BLOCK2_MOM5,
 BLOCK3_MOM4 AS BLOCK3_MOM5,
 BLOCK_DATE1_MOM4 as BLOCK_DATE1_MOM5,
 BLOCK_DATE2_MOM4 as BLOCK_DATE2_MOM5,
 BLOCK_DATE3_MOM4 as BLOCK_DATE3_MOM5
RESIDENT V4;
LEFT JOIN (V5) LOAD
 BASE_SEGMENT_NBR2 as BASE_SEGMENT_NBR5,
 Mid_14_2 as Mid_14_5,
 Replacement2 as Replacement5
RESIDENT V2;

V7:
LOAD CARD_NO,
 'P' as V7,
 LoadDate3 as LoadDate7,
 limit3 AS limit7,
 bal3 AS bal7,
 delq3 AS delq7,
 CARD3 AS CARD7,
 BLOCK1_MOM3 AS BLOCK1_MOM7,
 BLOCK2_MOM3 AS BLOCK2_MOM7,
 BLOCK3_MOM3 AS BLOCK3_MOM7,
 BLOCK_DATE1_MOM3 as BLOCK_DATE1_MOM7,
 BLOCK_DATE2_MOM3 as BLOCK_DATE2_MOM7,
 BLOCK_DATE3_MOM3 as BLOCK_DATE3_MOM7
RESIDENT V3;
CONCATENATE LOAD
 CARD_NO, 'P' as V7,
 LoadDate5 as LoadDate7,
 limit5 AS limit7,
 bal5 AS bal7,
 delq5 AS delq7,
 CARD5 as CARD7,
 BLOCK1_MOM5 AS BLOCK1_MOM7,
 BLOCK2_MOM5 AS BLOCK2_MOM7,
 BLOCK3_MOM5 AS BLOCK3_MOM7,
 BLOCK_DATE1_MOM5 as BLOCK_DATE1_MOM7,
 BLOCK_DATE2_MOM5 as BLOCK_DATE2_MOM7,
 BLOCK_DATE3_MOM5 as BLOCK_DATE3_MOM7
RESIDENT V5;

Regards,
Shumail Hussain

shumailh · ‎2009-08-05

Hi Stephen,

Thanks for your guidance, You are right these was a problem with table V5... values were not coming in that table, as i have mentioned in my above post.

Regards,
Shumail Hussain