Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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.
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
hope this helps