Load Multiple Tables and Subset of Tables

[#1]

Hi,

I have uploaded 3 original tables that are linked with unique keys.

Table1: Has 100 rows of raw data that contains some bad data. I want to create a clean table and link it to two other tables. This what I have done so far. This works partially.

Table1:

Load

     Column A, 

     DateTime,          //Contains Date and Time

     Condition_Key,

     StartTime,

     EndTime

FROM

[C:\.....

In the load script, I created a cleaner version of Table1 with the following:

CleanTable1:

     ColumnA,                    // Same name as in original Table1

     Date(Floor(DateTime)) As DateTime,

     Condition_Key AS CleanCondition_Key,     //  Relabeled this column for getting count of clean data

     StartTime AS CleanStartTime,                   // Relabeled this column for calculating Endtime - Starttime.

     EndTime As CleanEndTime                       // Relabeled to calculate duration

Resident Table1

Where interval(EndTime - StartTime, 'm') > 25;

Table2:

Load

     Condition_Key,

     Repro_Key,

     Scope_Key

From

[C:\...

Table3:

Load

     Scope_Key

     Scope_Model

From

[C:\....

Table 1 and Table2 are linked with "Condition_Key". How do I link "CleanTable1" with Table 2 to get accurate count of "Repro_Key" and count of "Scope_Key" I tried using another column, but it create a loop and QlikView removed the unnecessary loop. I copied "Condition_Key" in Table2 and added another column and relabeled it to "CleanCondition_Key so it can link it to the same column name in CleanTable1. It did not work.

I am not an expert in QlikView. I do programming and have created several Charts using expressions. This is the first time I am using script during load. Some of these tables are large. I have a feeling I am not loading the table correctly. Can someone please guide?

Thanks,

[#2]

I do not beleive there is any reason to relabel the key field in the clean table

is there a reson why you did not load the original table 1 using the same where clause as as the clean table and the same date function around the date as used in the clean table?

It sound slike the clean table is just a subset of the original - I would name the fields the same if you have to do the resident load, but then drop table 1 - sounds like you are not using it if you create the clean version

[#3]

Hi Adam,

I am using both the raw table and the clean table for count. If I use the where clause in the original table, I do not get accurate count of original data. If I use the same column name, I do not get accurate counts. I cannot drop any table. You are correct, CleanTable is a subset of the original table.

[#4]

If you need both tables, then you cannot have the same key field because then table 1 and clean table will join together

You may need to create a new key field and link to that field

There is a good paper on best practices in data modeling -see link below

https://community.qlik.com/servlet/JiveServlet/previewBody/7343-102-1-9596/Best%20Practices%20in%20D...

hope this helps