topic Re: How to join 5 tables carrying almost same data?? in QlikView

How to join 5 tables carrying almost same data??

Anonymous — Tue, 14 Oct 2014 21:57:52 GMT

Hi,

I have 5 tables coming from excel carrying the data each year like 2009.... to .2014.

These tables carry same columns but data is different according to the year.Can any one suggest me how to avoid synthetic key and I am wondering whether to join or concatenate, If possible pls explain with example.

Your help is appreciated.

Regards,

Sharma.

Re: How to join 5 tables carrying almost same data??

JonnyPoole — Tue, 14 Oct 2014 22:14:44 GMT

In this sscenario its usually better to concatenate the files together. This will have effect of merging the rows in all spreadsheets into one spreadsheet with all the rows and no extra columns. It's as if you had manually added all the rows from each spreadsheet into 1 big spreadsheet first.

Joining would be done if the former operation would cacause duplication of records. You would join when you have extra fields in the 2nd table that you want to add onto the records from the 1st table. In this scenario you don't add rows, just columns to the table.

Re: How to join 5 tables carrying almost same data??

Anonymous — Tue, 14 Oct 2014 22:20:05 GMT

Ok Thanks Jonathan .Much appreciated.

So you reckon Concatenate is the best bet and how about synthetic keys or any perfomance issue ?

Re: How to join 5 tables carrying almost same data??

JonnyPoole — Tue, 14 Oct 2014 22:27:07 GMT

If you have other tables in the data model they will all Join to the resulting fact table on any field named the same. Use aliases to form the joins you want. In case of a need for a compsound join you can use autonumber() function to build one.

Regular performance optimization rules apply such as removing unneeded fields, agaggregating unneeded uniqueness, (convert dates with timestamps to just dates) ...

Re: How to join 5 tables carrying almost same data??

Anonymous — Tue, 14 Oct 2014 22:32:40 GMT

Thanks Jonathan. I have a similar document which has concatenated the tables and finally store into one qvd.

Indeed a quick reply.

Thanks.

Re: How to join 5 tables carrying almost same data??

Anonymous — Tue, 14 Oct 2014 22:43:04 GMT

Hi Jonathan ,

Just a quick question I have just realised the data is changing not just year , it is changing every month from 2009 to 2014 ,If i am to concatenate the script will be too big and Any advice pls.

Re: How to join 5 tables carrying almost same data??

JonnyPoole — Tue, 14 Oct 2014 22:45:43 GMT

Stick with the same approach but do a for loop in the script to say, load every file in a given directory. I'm writing from a phone and can't do an effective search for you this moment but that would be the best way.

Re: How to join 5 tables carrying almost same data??

Anonymous — Tue, 14 Oct 2014 22:51:32 GMT

Ok thanks for your efforts.

Re: How to join 5 tables carrying almost same data??

stevedark — Tue, 14 Oct 2014 23:09:30 GMT

Hi,

You may want to look at this blog post on dealing with a drop folder full of near identical files:

http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

Hope you find it useful.

Steve

Re: How to join 5 tables carrying almost same data??

Anonymous — Mon, 20 Oct 2014 23:52:19 GMT

Hi steve,

I need to set up a config file using variables by giving all the useful information like connection string and database name or setting a target value (which will be changed by the users accordingly)etc which sits outside the qlikview and these variables will pick up the changes automatically depending on the different environments we work like Dev,UAT and Prod.

Wondering if any one has a code how to write it.

Thank you,

Regards,

Sharma.

Re: How to join 5 tables carrying almost same data??

stevedark — Tue, 21 Oct 2014 23:15:52 GMT

Hi Sharma,

Please see this post on include files:

ht tp://www.quickintelligence.co.uk/qlikview-include-files/

It may point you in the right direction.

Steve

Re: How to join 5 tables carrying almost same data??

Anonymous — Tue, 21 Oct 2014 23:29:19 GMT

Hi steve,

I need to check some tables coming from the excel and i devevloped a code which checks the date validation and number field validation but i am wondering how would i Incorporate at qvd level to check this validation .I need a multiple iff condition to check this.

Can you help me pls

Regards,

Sharma.

Re: How to join 5 tables carrying almost same data??

Anonymous — Tue, 21 Oct 2014 23:31:33 GMT

Hi steve,

This is my table

GS1:

LOAD rowno(),
     date([Work Order Interruption Start Date],'DD MMM YYYY') as OBJ_GSoS_StartDate,
     Time([Work Order Interruption Start Time],'HH MM SS') as OBJ_GSoS_StartTime,
     date([Work Order Interruption End Date],'DD MMM YYYY') as OBJ_GSoS_EndDate,

From
BI Industrialisation\Data Files\GSOP\GS1\GS1 - Supply Restoration - MI - 2014-15.xls]
(biff, embedded labels, header is 20 lines, table is [TOT FAILURES GS1 YTD$])
WHERE [IN/ OUT/ QUERY Following Investigation]='OUT';

This is my table when i am loading the data in qlikview , I need to write a code that will check the above date field or isnum field are in write format.

if all of the fields are in right format

Store GS1 into correct.qvd

if any other fields are in wrong format it should

store GS1 into incorrect.qvd and this should send a message or alert the user that this needs correction.

Your help is indeed appreciated,

Regards,

Sharma.

Re: How to join 5 tables carrying almost same data??

Wed, 05 Nov 2014 12:37:46 GMT

Store each table data into different Qvd, later you can concatenate all the qvd’s.

after concatenating the qvd's again store them into original qvd, once you enter the new data ,that also will concatenate with the old data and it will store in the original data.