Practical problem (avoiding Sync)

[xtrimf]

Hi,

I Have 3 tables:

Users: UserID, CampaignID, Timestamp (time of creation)

Billing: UserID, Amount, Timestamp (time of billing)

TimeTable: Timestamp, date,day,time...

This schema creates a sync table.

My problem is that I need to show "Billing per CampaignID" on a TimeLine.

I cannot concatenate Uers & Billing because then it will not work (the report).

I cannot join Users & Billing becasue the timestamps are different.

I do not want to join with TimeTable (millions of records....)

Any suggestions of how to achieve this?

I know that one sync table is not a big issue...but this is just a miniature sample of my problem that hopefully ' once solved, will solve all my problems.

[#2]

Hi

The sync table is created because you have more than one field in tables where the field name is the same, so you have to rename those fields to avoid the sync table. In SQL then, I would do something like:

The link between Users & Billing should be by way of the userid, therefore, rename the Timestamp fields in each case to something like Timestamp_Users and Timestamp_Billing.

I'm not sure how you are linking to the TimeTable because it would be unusual to use the timestamp to do this, but if this is the case then probably you need to create the same fields in the TimeTable table.

Your full script would look something like:

Users:
LOAD UserID, CampaignID, TimeStamp AS TimeStamp_Users;
SQL SELECT UserID, CampaignID, Timestamp FROM ..........
Billing:
LOAD UserID, Amount, TimeStamp AS TimeStamp_Billing;
SQL SELECT UserID, Amount, Timestamp FROM ......
TimeTable:
LOAD TimeStamp AS TimeStamp_Users, TimeStamp AS TimeStamp_Billing, Date, Day, Time FROM .....

This will remove any Sync tables and also remove any cyclic references.

Good luck,

[xtrimf]

Hi,

Your way produces an error in qlikview

Users:
LOAD * INLINE [
 UserID, Timestamp_Users, Campaign
];

Billing:
LOAD * INLINE [
 UserID, Timestamp_Billing, Amount
];

TimeTable:
LOAD * INLINE [
 Timestamp, Timestamp_Users, Timestamp_Billing
];

I'm not sure how you are linking to the TimeTable because it would be unusual to use the timestamp to do this, but if this is the case then probably you need to create the same fields in the TimeTable table.

How would you suggest to do it?

[#4]

Have you added any sample data to your LOAD * INLINE commands above?

I haven't tried it but it wouldn't surprise me if the above, on its own, would not work because there is no data. If you have sample data, can you post the whole script because it really shouldn't have any problems.

The reason that I stated that using a timestamp would be unusual is that normally a timestamp is row based and would change for each row in each table, but if you have timestamps that are identical then it should be no problem.

[xtrimf]

yes, I had some made up data.

Still wont work,

I just input some random numbers..

[#6]

Yes, sorry about that, my advice here is completely wrong, and now I feel a little guilty about it !!

Can you explain what model you expect to see, which tables should join to which tables?

[#7]

If you are really wanting to not join to the time table then probably the following would work.

Users:
LOAD * INLINE
[UserID,Timestamp_Users,Campaign
NCW,01012010,A
AJG,02012010,B
NCW,03012010,B
GTH,03012010,C
];
Billing:
LOAD * INLINE
[UserID,Timestamp_Billing,Amount
NCW,01012010,12569.24
AJG,02012010,5487.25
NCW,03012010,84569.1
GTH,03012010.648712.3
];
TimeTable:
LOAD * INLINE
[Timestamp,Day,Date,Time
01012010,Monday,01/01/2010,09:25
02012010,Tuesday,02/01/2010,09:44
03012010,Wednesday,03/01/2010,10:01
];

This leaves the TimeTable out on its on in the model, i.e. it is not joined to anything, and the users and billing tables are joined only on userid.

[xtrimf]

Hi Nigel, The Idea is to have a single time field that will "control" the entire model.

Leaving the TimeTable "Hanging" will not do it....

I will describe the full problem now.

One User can have more then one campaign...

I need the report "billing per campaign per day"

It look like any rename will cause a loop...

There are millions of records, so I would like to avoid doubling fields

[#9]

May be you can Left Join the Campaigns table to the Billing table anduse the TimeTable timestamp linked with the billing table.