topic Re: Merging two tables vertically in QlikView

Merging two tables vertically

hibhardwaj — Mon, 13 May 2013 21:38:35 GMT

I have two tables:

TableA			TableB
Field A	Field B	Field C	Field A	Field B	Field C
1	abc	njn	4	nam	asl
2	nka	nmd	5	afj	adt
3	ada	mbm	6	fba	geh

The columns in the two tables are identical, but the actual entries in the rows may or may not be same.

What I want to do with these two tables is just to concatenate TableA with TableB. The result should look like this:

TableC
Field A	Field B	Field C
1	abc	njn
2	nka	nmd
3	ada	mbm
4	nam	asl
5	afj	adt
6	fba	geh

To me this looks real simple, but I am not able to do it. Please let me know if you need any further clarification on what I want to accomplish.

Re: Merging two tables vertically

CELAMBARASAN — Mon, 13 May 2013 21:51:48 GMT

ConsolidateTable:

Load

FieldA,

FieldB,

FiledC

From TableA;

Load

FieldA,

FieldB,

FiledC

From TableB;

If Field names and # of fields are same then QV automatically concatenates with the first loaded table

Re: Merging two tables vertically

hibhardwaj — Mon, 13 May 2013 21:57:59 GMT

Thanks CA for you reply.

I tried doing that, but the data from the second table is still missing in the final table.

In my case, the first table is pulled from a qvd file and the second table is coming from a resident table with some 'where' conditions.

Dont know what should I do to make it show the entries from both tables.

Re: Merging two tables vertically

CELAMBARASAN — Mon, 13 May 2013 22:01:43 GMT

Can you post the Script used from your end?

And also check your table viewer, Is that both tables loaded separately?

Re: Merging two tables vertically

hibhardwaj — Mon, 13 May 2013 22:09:22 GMT

Sure! The table that is coming from the QVD file gives the data for everything but the last week. And I am using ODBC connection + the resident load to get the data for the last week.

I want a master table that has past data + last week's data. Thanks again for you time sir!

Here is the script:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET CarriedDate= (Purgechar(Date((today()-1),'YYYY.MM.DD' ),'.'));

ODBC CONNECT32 TO XYZ2 (XUserId is cSPcVYRNJbaMXUVMXDZA, XPassword is GbYRZWBORDbSWVROFbYQWYQI);

DataMaster:

SELECT "PHPICK00"."PHWHSE","PHPICK00"."PHPCTL", "PDPICK00"."PDPIQT", "STSTYL00"."STSC10", "PDPICK00"."PDSTYL", "PDPICK00"."PDSSFX", "STSTYL00"."STSTYD", "STSTYL00"."STPRC", "STSTYL00"."STRPRC", "PHPICK00"."PHSHDT",

"PDPICK00"."PDPDTP", "PHPICK00"."PHCO"

FROM ("MEMPHIS2"."PKMANH221D"."PHPICK00" "PHPICK00" INNER JOIN "MEMPHIS2"."PKMANH221D"."PDPICK00" "PDPICK00" ON (("PHPICK00"."PHCO"="PDPICK00"."PDCO") AND ("PHPICK00"."PHDIV"="PDPICK00"."PDDIV")) AND ("PHPICK00"."PHPCTL"="PDPICK00"."PDPCTL")) INNER JOIN "MEMPHIS2"."PKMANH221D"."STSTYL00" "STSTYL00" ON (("PDPICK00"."PDCO"="STSTYL00"."STCO") AND ("PDPICK00"."PDSTYL"="STSTYL00"."STSTYL")) AND ("PDPICK00"."PDSSFX"="STSTYL00"."STSSFX")

WHERE "PHPICK00"."PHASLC"='Y' AND "PDPICK00"."PDPDTP"='VAS' AND "PDPICK00"."PDPIQT">0 AND ("PHPICK00"."PHWHSE"='OB2' OR "PHPICK00"."PHWHSE"='SCC') and "STSTYL00"."STSC10"<>''

and "PHPICK00"."PHSHDT">=20130506

and "PHPICK00"."PHSHDT"<=20130512

ORDER BY "STSTYL00"."STSC10", "PDPICK00"."PDSTYL", "PDPICK00"."PDPCTL";

PZData:

LOAD [PZ Indicator],

Description,

Brand,

TotalSum,

date#( RangeDate, 'YYYYMMDD') as Date

FROM

PZData.qvd

(qvd);

Load

STSC10 as [PZ Indicator],

STSTYD as Description,

PDSSFX as [Brand],

PDPIQT as TotalSum,

PHSHDT as Date

Resident DataMaster;

Drop Table DataMaster;

Re: Merging two tables vertically

CELAMBARASAN — Mon, 13 May 2013 22:13:18 GMT

Can you do a simple test.

Capture a table viewer by without dropping the DataMaster table and check whether it has any data?

Also Is all tables loaded separately?(In your case After commenting out the Drop table table statement there will be 3 tables )

Re: Merging two tables vertically

hibhardwaj — Mon, 13 May 2013 22:21:55 GMT

After commenting out the drop table command, I rand the report again. The result is same- my final table (PZData) doesnt have the data for last week.

But when I opened the DataMaster, I saw that the data is there and is accurate too! What is happening here, my head is spinning badly.

Please let me know if you need any other details.

Re: Merging two tables vertically

hibhardwaj — Mon, 13 May 2013 22:35:44 GMT

Actually I ran it without dropping the table and it started showing the result in the table. I changed the format a little bit and the numbers are right there!

Thanks for investing time in this problem CA....