Joining a resident table to original table

[#1]

Hi

I've got the following script in my QVW:

[Transaction Data]:

LOAD TXN_ID,

  Date(Date#(left(TXN_ID,8),'YYYYMMDD'),'DD/MM/YYYY') as [Sale Date],

     TXN_CODE,

     SALES_DATE,

     Date(Date#(right(SALES_DATE,5),'hh:mm'),'hh:mm') as [Sale Time],

   ...

FROM

[dummy transaction data.xls]

(biff, embedded labels, table is [Export Worksheet$]);

[Transaction Date]:

LOAD [Sale Date],

  [Sale Time],

  Year([Sale Date]) as [Sale Year],

  MONTH([Sale Date]) as [Sale Month],

  DAY([Sale Date]) as [Sale Day],

  Hour([Sale Time]) & ':00-' & Hour([Sale Time]) & ':59' As [Sale Time Period]

Resident [Transaction Data];

Is there any way to Join the Resident table [Transaction Date] to the first table [Transaction Data]? I've tried to use the code "left join ([Transaction Data])" but the results in my Sales Value become invalid (e.g. Sales Value for Internet purchases becomes $$3327636.55 instead of $885371.55)

[ramoncova06]

haven't seen the document, but at a quick glance I can assume that the issue with the left join is because is only being applied on the field [Sale Date], you probably want to add TXN_ID, TXN_CODE in there

[Transaction Date]:

left join([Transaction Data])

LOAD [Sale Date],

  [Sale Time],

  Year([Sale Date]) as [Sale Year],

  MONTH([Sale Date]) as [Sale Month],

  DAY([Sale Date]) as [Sale Day],

  Hour([Sale Time]) & ':00-' & Hour([Sale Time]) & ':59' As [Sale Time Period],

TXN_ID,

TXN_CODE

Resident [Transaction Data];

[r_wroblewski]

Hi Chay Him,

I agree with Ramon.

Not using TXN_ID and/or TXN_CODE will mix up your Sales Values.

Just include them in your Resident Table and let us know if this fix your problem.

Best regards,

Ronny

[danieloberbilli]

I also cant open the file at the moment, but I agree with Ramon.

For your 'Internet Purchase' you probably have 4 different Sales cases at the same date and time - so the join results in accidentially adding more rows.  

Maybe also check if the Date and Time fields are interpreted by Qlik in the same way in both tables - might be that one is a timestamp, another one is currently nothing but a string...

[#5]

Hi Ramon

Added in both fields, but I'm still a good $100,000 over the correct amount.

[r_wroblewski]

Hi Chay Him,

it seems that also with this some rows mixing up during join.

My advice is that you should create a unique key(e.g. TX_CODE-TX_ID-[Sale Date]-[Sale Time]) before you join the tables. Just check before, which fields you need for creating such an unique key for each row. After joining you just can drop the key field if you don`t need it anymore.

Best regards,

Ronny

[ramoncova06]

so fixed the issue my my leased license and now I am able to see your script

in this case I do not recommend doing joins just to prevent the issue that is currently happening to you (I have some performance concerns with doing left joins to big tables also), I think that is better to create a temp table add the new fields and then drop the previous table and rename the temp table to the original name table

here is one example

noconcatenate

[Temp Transaction Date]:

  load

      TXN_ID,

      [Sale Date],

     TXN_CODE,

     SALES_DATE,

      [Sale Time],

     PATRON_ID,

     CHANNEL_TYPE,

     REFERENCED_TXN_ID,

     PRODUCT_ID,

     TICKETS_SOLD,

     PAYMENT_METHOD,

     PAID_AMT,

     POS_ALIAS,

     Year([Sale Date]) as [Sale Year],

  MONTH([Sale Date]) as [Sale Month],

  DAY([Sale Date]) as [Sale Day],

  Hour([Sale Time]) & ':00-' & Hour([Sale Time]) & ':59' As [Sale Time Period]

Resident [Transaction Data];

drop table  [Transaction Data];

rename table [Temp Transaction Date] to [Transaction Data];

[petter · Accepted solution]

Having had a quick look at the Load Script and the data model it seems to me that you don't need to do a join at all to add the extra column called [Sale time Period] and the three sub components of Date. That can easily be achieved by using a preceding load like this (inserting line 2-8):

[Transaction Data]:
LOAD
    *,
    Year([Sale Date]) as [Sale Year],
    MONTH([Sale Date]) as [Sale Month],
    DAY([Sale Date]) as [Sale Day],
    Hour([Sale Time]) & ':00-' & Hour([Sale Time]) & ':59' As [Sale Time Period]
;  // The following LOAD will feed it's data into the LOAD above and produce the final table....
LOAD
    TXN_ID,
    Date(Date#(left(TXN_ID,8),'YYYYMMDD'),'DD/MM/YYYY') as [Sale Date],
    TXN_CODE,
    SALES_DATE,
    Date(Date#(right(SALES_DATE,5),'hh:mm'),'hh:mm') as [Sale Time],
    PATRON_ID,
    CHANNEL_TYPE,
    REFERENCED_TXN_ID,
    PRODUCT_ID,
    TICKETS_SOLD,
    PAYMENT_METHOD,
    PAID_AMT,
    POS_ALIAS
FROM

(biff, embedded labels, table is [Export Worksheet$]);

I did a quick modification of your load script using a BINARY (load) to keep the data without reloading from your source and then modified it. This seems to work fine and you will get the $885371.55 sum and a bit simpler data model that should work fine for you.

[#9]

Thanks for the solution Petter, didn't understand preceding load previously till a colleague told me about it yesterday!