How to add a column with multiple values to a existing QVD?

[#1]

Hi,

Below is my load script

LOAD

  [Date Id],

  [Traffic],

  [Loc #]

FROM ..\..\Data\QVD\Transform\StoreTraffic_LocWk.qvd (Qvd);

Now i have to add a column named TranTypeID and it must have the values of 1,2.

Foe example something like this

LOAD

  [Date Id],

  [Traffic],

  [Loc #],

     '1' as [TransactionTypeId],

     '2' as [TransactionTypeId]

FROM ..\..\Data\QVD\Transform\StoreTraffic_LocWk.qvd (Qvd);

But i cannot do the above way as we cannot repeat the column name more than once.

How do i add that to this current script?

Can someone please throw any suggestions?

Thanks

[#2]

Hi Avinash,

The above script works fine but if i do sum(traffic) its giving me the traffic value multiplied by 4 times but if i select transaction type 1 and channel POS then i get correct value.

But even if i dont select any i want to see the correct value.

Any suggestions?

Thanks

[lawrenceiow]

Apologies Qlikview Developer, I did not notice that you use a different source in your second and third loads. If you use the method Avinash R gives below with Subfield('POS;CONCIERGE',';') as TransactionChannelDesc does that give what you want?

[#4]

Hi Lawrence,

If i use the solution suggested by avinash i get the sum(traffic) as double the traffic but if i select one of the trantype i get the correct value.Actually i think i am making it more complicated than what it actually is, basically i am trying to join multiple facts into a single table which doesnt have same dimension columns,if someone can help me with that it would be great.

Thanks

[lawrenceiow]

You are loading Traffic from "StoreTraffic_LocWk.qvd" twice so you will get double the traffic. How are you distinguishing which Traffic values should belong to which TransactionTypeId? Are you sure you shouldn't be adding a Where after your From?

In other words;

Load from StoreTraffic_LocWk.qvd where (some criteria) and add 1 as TransID and POS as TransDesc

then

Load from StoreTraffic_LocWk.qvd where (criteria not met) and add 2 as TransID and CONCIERGE as TransDesc

[#6]

Hi Lawrence,

This one should work fine, but like i have said my end goal is to join the sales and traffic facts into a single table. Can you please help me achieve that?

Thanks

[#7]

Hi,

Given to your data model irrespective you are going to have that problem.

As you are adding two TransactionTypeId to the same record in an ideal world. If i was you rather than using Sum(Traffic) i would use Sum(if(TransactionTypeId = 1, Traffic))  that should take care of the multiple records assuming you scripted its right.

Hope that helps!

Regards

Rahul

[#8]

Hi rahul,

My final idea is basically to merge multiple facts into a single table but my facts doesnt have same dimension columns, Can you please help me to achieve it?

Thanks

[avinashelite]

Hi,

We are missing some where i think ,Can you please post your app.

[#10]

Hi,

I believe it very important to understand what are you planning to do with these two static TransactionID and why you believe they need to be a part of your data model.

With what i have understood is that you want a TransactionID to appear against each record in you fact table and have a value of 1 and 2 (creating one to many relationship). Depending on what to plan to do with the TransactionID at the end of the day i believe you could segregate that one field into two i.e. Tran_ID_a that holds the value of 1 and Tran_ID_b that holds the value of 2 this should take care of multiple records. However as I said,  I do not know what you will use them for so cannot say if that solve the problem.

As far as your comment of merging the fact tables is concerned you do not need them to have the same dimensions. You could use the Key Field and /or Multiple Fields to do a left join into your table till the time you are maintaining a one to one or Many to One relationship. What you cannot do is have 'Many to Many' or 'One to Many' relationship at any point in your script as that will lead to record multiplication.

Regards

Rahul