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

[joshabbott]

Do you want the column name to be the same, or do you want to have two records (a 1 and a 2) for each row in the original load?

If you want two values, you just can load the table two times, The first time with 1 as TransactionTypeID, and the second with 2.  You can let QlikView's automatic concatenation concatenate the loads together for you.

[#3]

Hi Josh,

below is what i did

Sales:

LOAD

  [Date Id] ,

  [Loc #],

  [TransactionTypeId],

  [TransactionChannelDesc],

  [Transaction Count],

  [Units],

  [Gross $],

  [Discount $],

  [Net $],

  [Cost $],

  [Margin $]

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

Concatenate (Sales)

LOAD

  [Date Id],

  [Traffic],

  [Loc #],

     1 as [TransactionTypeId],

  'POS' AS [TransactionChannelDesc]

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

Concatenate(Sales)

LOAD

  [Date Id],

  [Traffic],

  [Loc #],

     2 AS [TransactionTypeId],

  'CONCIERGE' AS [TransactionChannelDesc]

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

I want the transactiontype id to be 1 for some and 2 for some, the reason i am doing this is because i want to have all my facts in a single table.

But with my above approach if i do SUM(Traffic) i get higher values ,however if i select either 1 or 2 i get correct.

Is there any way i can achieve it?

Thanks

[stigchel]

Instead of a concatenate (adding rows) use a join load (add fields/columns)

[lawrenceiow]

So "Traffic" is a Measure and not a Dimension? Therefore do not repeat [Traffic] in your second Concatenate LOAD.

[#6]

Hi Lawrence,

If i dont repeat Traffic ( which is a measure) in my second concatenate then if i select the second trantype the traffic goes blank which shouldnt happen.

Any suggestions?

Thanks

[#7]

Hi Piet,

Sorry i didnt get you.Can you please explain with an example?

Thanks

[avinashelite]

Hi,

Try like this in your script it will do the job :

LOAD

  [Date Id],

  [Traffic],

  [Loc #],

Subfiled('1;2',';') as [TransactionTypeId]

from .....

[stigchel]

I think you actually don't have to join and reading between the lines I think this is what you want. If not I don't understand ..

See last field of the below load

Sales:

LOAD

  [Date Id] ,

  [Loc #],

  [TransactionTypeId],

  [TransactionChannelDesc],

  [Transaction Count],

  [Units],

  [Gross $],

  [Discount $],

  [Net $],

  [Cost $],

  [Margin $],

  if([TransactionChannelDesc]='POS', 1 ,if([TransactionChannelDesc]='CONCIERGE',2)) as [TransactionTypeId]

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

[avinashelite]

Try like this in your script it will do the job :

LOAD

  [Date Id],

  [Traffic],

  [Loc #],

Subfiled('1;POS',';') as [TransactionTypeId]

from .....