Data Loading

[#1]

Hi,

I am new to this Qlikview 8.5 developer and we are working on a Telecom company calls analysis. I am having difficult time with the data as I am dealing with millions of records as a summary from tables.

There are strange problems I am facing like upon reload after any changes the summaries are producing incorrect figures and when I load single table and see the related dashboard it gives perfect summaries but when I add more tables it produces false results. I've noticed Syn keys in my tables almost more then 8 syn keys....can u please suggest me the loading methodlogies and also guide me DO's and DONTS.

In every table I want relationship only on LDI_CD & TERM_CD and pulling data YEAR/MONTH wise and summary column is CALL_DURATION, TOTAL_TIME. I think because of the similarities in other fields its creating auto relationship which is unwanted. Help me to solve this problem.

Attached is my table structure for your perusal please suggest.

[#2]

Hi,

I would suggest remove all the unwanted joins either by renaming similar columns in one of the table or by removing it from one of the table if not required.

Also, create a new field in tables where you have both LDI_CD and TERM_CD, this field should be concatenation of LDI_CD and TERM_CD. All such tables should be joined on this new field and LDi_CD and TERM_CD be removed.

LDIOPERATORS and TERMOPERATORS tables could be merged into a single table which will have the combined key, LDI_CD and TERM_CD apart from other fields.

Let me know should you have any doubt/query

Thanks

Amit

[#3]

Amit thanks a lot...can you tell me how to load the data...I am loading data in Script like this.

ODBC CONNECT TO [ICH_LIVE;DBQ=TELCOSU];

SQL SELECT *
FROM LDIOPERATORS;;
//************************************************
SQL SELECT *
FROM TERMOPERATORS;;

SQL SELECT *
FROM "IPDATASUM_VIEW";;
//************************************************

SQL SELECT *
FROM "VW_PRE_PROBE_SUMMARY";

SQL SELECT *
FROM "VW_INCOMING";

Do I need to use the LOAD statement...I would appreciate if you can give me the example using my script ...thanks a lot.

[#4]

Hello,

have also a look for QUALIFY and UNQUALIFY.

As you see in your application QlikView uses same field names as a key to connect the tables. That are your main proiblem here.

Concatenate the key you want.

Qualify *;

Unqualify %MyNewKey;

Load *,

LDI_CD &'_' &TERM_CD as %MyNewKey,

Field3,

Field4;

SQL Select * from MyTable;

Unqualify *;

Good luck!

Rainer

[johnw]

Any field with the same name is considered by QlikView to be the same field. So that's the reason you're getting all the links.

As for what to do about it, that depends heavily on your data model, which I don't fully understand.

It sounds like your base data is just calls, such as one record per call. It sounds like that was considered too much data to load into QlikView, so instead you made summary tables. But you made three different summary tables. Three different tables summarizing the same information. If so, I'd say that's the root of your problem. It seems to me you should only have one summary table that includes all of the fields you may ever want to summarize by, rather than three summary tables, each with a subset of the fields you might want to summarize by.

Or, and again if I have the situation right, you could keep your multiple summaries. But you'd have to name the common fields differently so that they don't link up, and you're basically creating three different applications in one.

But I could be seriously misunderstanding the situation, and could therefore be giving really bad advice.

The basic approach to avoiding undesired links, loops and synthetic keys is simply to use a clean data model. One approach is to use highly-normalized data. QlikView deals fine with that. Another is to build a central fact table that pulls in or links to a lot of other information. A fact table like that can actually be very denormalized, because QlikView's compression gets around the problems of denormalization. To me, any undesired link, loop or synthetic key just tells me that I've messed up the data model.

[#6]

humm...well I am dealing with billions of records and all the summaries are different and for different purposes so therefore cant be merged into one. As there is traffic analysis going on and reconciliation going on and so much. Anyway

What I want to ask is to get rid of syn keys do I need to do something like this. I am sorry I dont know much options like RESIDENT and LOAD * and all...so please be patient with me..thanks 🙂 just one example with my real table would appreciate.

Load

field1,
field2,
field3,
field4,
field5,
field6
field7,
field8,
SQL SELECT *
FROM "IPDATASUM_VIEW";

[#7]

I have searched QUALIFY & UNQUALIFY after "Rainer Filoda" earlier reply but dont know exactly how to do it so trying to figure it out. I think I need to qualify and unqualify manually rather qlikview to generate it for me.

[#8]

Well finally I came up with the following script which is giving me one SYN key combined with the 4 key fields as desired.

ODBC CONNECT TO [ICH_LIVE;DBQ=TELCOSU];

QUALIFY *;

UNQUALIFY YEARS, LDI_CD, TERM_CD, SOURCE;

SQL SELECT *
FROM LDIOPERATORS;;

SQL SELECT *
FROM TERMOPERATORS;;

SQL SELECT *
FROM "IPDATASUM_VIEW";;


SQL SELECT *
FROM "VW_PRE_PROBE_SUMMARY";

SQL SELECT *
FROM "VW_INCOMING";

please suggest am i doing right or what more can I do to make it better?

[johnw · Accepted solution]

Sounds good. Qualifying some of the data is a reasonable approach. That's pretty much the second alternative I suggested, "...name the common fields differently so that they don't link up, and you're basically creating three different applications in one." Using the Qualify automatically names the fields differently. I'd probably rename the fields myself using AS rather than use Qualify, but the end result will be pretty much the same. It sounds like a reasonable solution for your case.