Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Concatenate Two Crosstables

Hi!

Thanks for clicking on this question. I have this script:

Parts:

CrossTable([Last Name], [Parts AOP Data])

LOAD [FiscalMonthYear] as [Fiscal Month Year],

     LastNameC,

     LastNameRo,

     LastNameA,

     LastNameG,

     LastNameW,

     LastNameG,

     LastNameP,

     LastNameRe,

     LastNameH,

     LastNameT

    // Sum

    // M,

     //N,

     //O,

     //P,

    // Q

FROM

(ooxml, embedded labels, table is [By name]);

PHH:

CrossTable([FiscalMonthYear], PHHData, 1)

LOAD //[Client #],

    // [Unit #],

    // [Current Breakdown],

     Capitalize([Last Name])as [Last Name],

     //[Employee ID],

    // [Unique ID],

    // [First Name],

   //  VIN,

    // [Charge Category],

     [41640] as [JAN-14],

     [41671] as [FEB-14],

     [41702] as [MAR-14],

     [41733] as [APR-14],

     [41764] as [MAY-14],

     [41795] as [JUN-14],

     [41826] as [JUL-14],

     [41857] as [AUG-14],

     [41888] as [SEP-14],

     [41919] as [OCT-14],

     [41950] as [NOV-14],

     [41981] as [DEC-14]

    // YTD

FROM

(ooxml, embedded labels, header is 4 lines, table is PHH);

How do I concatenate these two tables? Ideally, I will have four filled-out columns:

1) FiscalMonthYear

2) Last Name

3) Parts AOP Data

4) PHH Data

This is the only way to stop looping and synthetic tables in my database. You can't just concatenate with the concatenate function because it'll say illegal combination of prefixes.Thanks for all your help!

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

How about CrossTable 'ing all your tables individually first with no concatenates,

Then concatenating the resultant tables from resident into your target table name.

Then dropping the temporary CrossTable 'd tables.

How to join two crosstab tables?

SCRIPT LOOKS LIKE THIS :

Parts_TEMP:

CrossTable([Last Name], [Parts AOP Data])

LOAD [FiscalMonthYear] as [Fiscal Month Year],

     LastNameC,

     LastNameRo,

     LastNameA,

     LastNameG,

     LastNameW,

     LastNameG,

     LastNameP,

     LastNameRe,

     LastNameH,

     LastNameT

    // Sum

    // M,

     //N,

     //O,

     //P,

    // Q

FROM

(ooxml, embedded labels, table is [By name]);

PHH_TEMP:

CrossTable([FiscalMonthYear], PHHData, 1)

LOAD //[Client #],

    // [Unit #],

    // [Current Breakdown],

     Capitalize([Last Name])as [Last Name],

     //[Employee ID],

    // [Unique ID],

    // [First Name],

   //  VIN,

    // [Charge Category],

     [41640] as [JAN-14],

     [41671] as [FEB-14],

     [41702] as [MAR-14],

     [41733] as [APR-14],

     [41764] as [MAY-14],

     [41795] as [JUN-14],

     [41826] as [JUL-14],

     [41857] as [AUG-14],

     [41888] as [SEP-14],

     [41919] as [OCT-14],

     [41950] as [NOV-14],

     [41981] as [DEC-14]

    // YTD

FROM

(ooxml, embedded labels, header is 4 lines, table is PHH);

NoConcatenate

END_TABLE:

LOAD *

RESIDENT  Parts_TEMP;


Concatenate(END_TABLE)


load *
Resident PHH_TEMP;

drop tableS Parts_TEMP,PHH_TEMP;



LET ME KNOW ONCE YOU ARE DONE.


tHANKS

kRISHNA

View solution in original post

1 Reply
Highlighted
Specialist III
Specialist III

How about CrossTable 'ing all your tables individually first with no concatenates,

Then concatenating the resultant tables from resident into your target table name.

Then dropping the temporary CrossTable 'd tables.

How to join two crosstab tables?

SCRIPT LOOKS LIKE THIS :

Parts_TEMP:

CrossTable([Last Name], [Parts AOP Data])

LOAD [FiscalMonthYear] as [Fiscal Month Year],

     LastNameC,

     LastNameRo,

     LastNameA,

     LastNameG,

     LastNameW,

     LastNameG,

     LastNameP,

     LastNameRe,

     LastNameH,

     LastNameT

    // Sum

    // M,

     //N,

     //O,

     //P,

    // Q

FROM

(ooxml, embedded labels, table is [By name]);

PHH_TEMP:

CrossTable([FiscalMonthYear], PHHData, 1)

LOAD //[Client #],

    // [Unit #],

    // [Current Breakdown],

     Capitalize([Last Name])as [Last Name],

     //[Employee ID],

    // [Unique ID],

    // [First Name],

   //  VIN,

    // [Charge Category],

     [41640] as [JAN-14],

     [41671] as [FEB-14],

     [41702] as [MAR-14],

     [41733] as [APR-14],

     [41764] as [MAY-14],

     [41795] as [JUN-14],

     [41826] as [JUL-14],

     [41857] as [AUG-14],

     [41888] as [SEP-14],

     [41919] as [OCT-14],

     [41950] as [NOV-14],

     [41981] as [DEC-14]

    // YTD

FROM

(ooxml, embedded labels, header is 4 lines, table is PHH);

NoConcatenate

END_TABLE:

LOAD *

RESIDENT  Parts_TEMP;


Concatenate(END_TABLE)


load *
Resident PHH_TEMP;

drop tableS Parts_TEMP,PHH_TEMP;



LET ME KNOW ONCE YOU ARE DONE.


tHANKS

kRISHNA

View solution in original post