topic Re: How to combined two tables without using joins in QlikView

How to combined two tables without using joins

Wed, 12 Sep 2012 11:27:30 GMT

Hi All,

I am currently trying to consolidate data from two different tables into on table each table has roughly around 1 Million Records, and a Key that is a date.

When I load data from their respective resident tables via a Left Join, my memory usage raises and the loading takes forever.

Here is the code that loads from the resident tables

[Final MATDOC]:
NOCONCATENATE
LOAD
     [Entered on_MATDOC],     
    [Mat Doc_MATDOC], 
     [Posting_MATDOC]
RESIDENT [TMP_ECC_MATDOCS];
LEFT JOIN
LOAD
     [Entered on_MATDOC],
     [Material Doc_MATDOCBW],
    [Posting Date_MATDOCBW]
RESIDENT [TMP_BW_MATDOCS];

[Final MATDOC]:
NOCONCATENATE
LOAD
    [Entered on_MATDOC],
    [Mat Doc_MATDOC],
    [Posting_MATDOC]
RESIDENT [TMP_ECC_MATDOCS];
LEFT JOIN
LOAD
    [Entered on_MATDOC],
    [Material Doc_MATDOCBW],
    [Posting Date_MATDOCBW]
RESIDENT [TMP_BW_MATDOCS];

What am I doing wrong in the above? I basically need to compare as follows:

PSeudo: If  [Mat Doc_MATDOC] = [Material Doc_MATDOCBW] then return 1 else 0 //I will count the number of ones to determine the percentage found based on total records in source

Any assistance will be highly appreciated

Re: How to combined two tables without using joins

hic — Wed, 12 Sep 2012 12:40:14 GMT

I would do something like the following, but this assumes that you only have one possible [Material Doc_MATDOCBW] per [Entered on_MATDOC]. Then you can make your comparison directly in the Final table.

MappingTable:

Mapping LOAD

[Entered on_MATDOC],

[Material Doc_MATDOCBW]

RESIDENT [TMP_BW_MATDOCS];

[Final MATDOC]:

LOAD

ApplyMap('MappingTable',[Entered on_MATDOC],null()) as [Material Doc_MATDOCBW],

[Entered on_MATDOC],

[Mat Doc_MATDOC],

[Posting_MATDOC]

RESIDENT [TMP_ECC_MATDOCS];

HIC

Re: How to combined two tables without using joins

alexpanjhc — Wed, 12 Sep 2012 14:25:27 GMT

try to move NOCONCATENATE before
[Final MATDOC]:

Re: How to combined two tables without using joins

Thu, 13 Sep 2012 05:06:45 GMT

Thanks for the help, i've tried the mapping load, but the on field doesn't exist in the resident table the mapping load uses.

here its the loads for the two resident tables i want to combine:

[TMP_ECC_MATDOCS]:
NOCONCATENATE
LOAD
[Entered on] as [Entered on_MATDOC],     
    [Material Doc] as [Mat Doc_MATDOC], 
[Posting Date] as [Posting_MATDOC]
RESIDENT [ECC_TABLE]
WHERE [Entered on] = $(reconFileDate) - 1;
[TMP_BW_MATDOCS]:
NOCONCATENATE
LOAD  
  [Entered on] as [Entered on_MATDOCBW],
[Material Doc_BW] as [Material Doc_MATDOCBW],
    [Posting Date_BW] as [Posting Date_MATDOCBW]
RESIDENT [BW_TABLE]
WHERE [Entered on] = $(reconFileDate) - 1;

Re: How to combined two tables without using joins

Thu, 13 Sep 2012 05:12:01 GMT

Here is the error I get using the mapping load:


 Field not found - <Entered on_MATDOC>

[Mapping Table]:
Mapping LOAD
  [Entered on_MATDOC],
  [Material Doc_MATDOCBW]
RESIDENT [TMP_BW_MATDOCS]

Re: How to combined two tables without using joins

somenathroy — Thu, 13 Sep 2012 05:56:49 GMT

You may CONCATE two tables instead of JOIN. Then comparisons are possible at Expression level .

Regards,

som

Re: How to combined two tables without using joins

Thu, 13 Sep 2012 06:03:30 GMT

Don't the fields need to be named the same between the two tables? (in which case the join will happen and create keys on each field)

Re: How to combined two tables without using joins

Jason_Michaelides — Thu, 13 Sep 2012 06:18:36 GMT

You seem to have a lot of consecutive RESIDENT loads in your script. Can you post your app so we can see the script? And maybe some sample source data...

Re: How to combined two tables without using joins

Thu, 13 Sep 2012 06:55:54 GMT

Hi Jason,

I won't be able to post sample data as it is company information, however here it some more code and my logic

Logic:

There are two source of data, i need to compare the two sources to see if they match. I.e. What System 1 has, must also me in System 2 (I need to keep the history of the two systems in order to do a successfully comparison.

1. Check if History QVD exists for System 1 -> If not, then load new data, if QVD exists, load the QVD and concate the new data

2. Do the same as step 1 for System 2, but add on relevant [Entered on] field to System 2 table (for delete/reload of same days data)

3. [Troublesome step]: combine certain fields into one table for comparing the two systems

4. Drop the resident tables (System 1 & 2)

Code: (The code below precedes the code i'm having trouble with)


///////////////////////////////////////////////////////////////////////////////////////
//     [START] - ECC DATA TABLE - Full load for ECC data       //
///////////////////////////////////////////////////////////////////////////////////////
IF(NOT isNull(QvdCreateTime('History\ECC_HISTORY.QVD'))) THEN //Does the History QVD for ECC Exist? If yes, then load that data first and append new data to it
 [TMP_ECCTABLE]:
 LOAD [Material Doc], 
       Mat_Doc_Year, 
      [Posting Date], 
      [Entered on], 
      [Entered at], 
      [Amount in LC], 
      Quantity
 FROM
 History\ECC_HISTORY.QVD (qvd)
 WHERE ([Entered on] <> ($(reconFileDate)-1) OR [Entered on] <> ($(reconFileDate)));
 
 Concatenate([TMP_ECCTABLE])
 LOAD @1 as [Material Doc], 
      @2 as Mat_Doc_Year, 
      @3 as [Posting Date], 
      @4 as [Entered on], 
      @5 as [Entered at], 
      @6 as [Amount in LC], 
      @7 as Quantity
 FROM
 $(zECCFileToArchive)
 (txt, codepage is 1252, no labels, delimiter is ',', msq);
 
 [ECC_TABLE]:
 NoConcatenate
 LOAD DISTINCT [Material Doc], 
       Mat_Doc_Year, 
      [Posting Date], 
      [Entered on], 
      [Entered at], 
      [Amount in LC], 
      Quantity
 RESIDENT [TMP_ECCTABLE];
 DROP TABLE [TMP_ECCTABLE];
 
ELSE //In place when model is reloaded for the first time and there's no history
 [ECC_TABLE]:
 LOAD @1 as [Material Doc], 
      @2 as Mat_Doc_Year, 
      @3 as [Posting Date], 
      @4 as [Entered on], 
      @5 as [Entered at], 
      @6 as [Amount in LC], 
      @7 as Quantity
 FROM
 $(zECCFileToArchive)
 (txt, codepage is 1252, no labels, delimiter is ',', msq);
ENDIF
STORE [ECC_TABLE] INTO "History\ECC_HISTORY.QVD" (qvd); //Store all data into the History QVD
///////////////////////////////////////////////////////////////////////////////////////
//     [END] - ECC DATA TABLE - Full load for ECC data       //
///////////////////////////////////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////////////////////////////////
//   [START] - BW DATA TABLE - Full load for BW data        //
///////////////////////////////////////////////////////////////////////////////////////
IF (NOT isNull(QvdCreateTime('History\BW_HISTORY.QVD'))) THEN //Does the History QVD for BW Exist? If yes, then load that data first and append new data to it
 [TMP_BWTABLE]:
 LOAD @1 as [Material Doc_BW],
      @2 as [Mat Doc Year_BW], 
      @3 as [Posting Date_BW], 
      @4 as [Total Stock - Receipt], 
      @5 as [Total Stock - Issue], 
      @6 as [Amount in LC_BW],
      @4 + @5 as Quantity_BW
 FROM
 YDIORCON.CSV
 (txt, codepage is 1252, no labels, delimiter is ',', msq);
 LEFT JOIN (TMP_BWTABLE)
 LOAD 
   [Material Doc] as [Material Doc_BW],
  [Entered on]
 RESIDENT [ECC_TABLE];
 [TMP_BWTABLE2]:
 NoConcatenate
 LOAD
   [Material Doc_BW],
      [Mat Doc Year_BW], 
      [Posting Date_BW], 
      [Entered on],
      [Total Stock - Receipt], 
      [Total Stock - Issue], 
      [Amount in LC_BW],
      Quantity_BW
 FROM History\BW_HISTORY.QVD (qvd);
  
 Concatenate([TMP_BWTABLE2])
 LOAD [Material Doc_BW],
      [Mat Doc Year_BW], 
      [Posting Date_BW], 
      [Entered on],
      [Total Stock - Receipt], 
      [Total Stock - Issue], 
      [Amount in LC_BW],
      Quantity_BW
 RESIDENT [TMP_BWTABLE]
 WHERE ([Entered on] <> ($(reconFileDate)-1) OR [Entered on] <> ($(reconFileDate)));
 
 
 [BW_TABLE]:
 NOCONCATENATE
 LOAD DISTINCT [Material Doc_BW],
      [Mat Doc Year_BW], 
      [Posting Date_BW], 
      [Entered on],
      [Total Stock - Receipt], 
      [Total Stock - Issue], 
      [Amount in LC_BW],
      Quantity_BW
 RESIDENT [TMP_BWTABLE2];
  
 DROP TABLE [TMP_BWTABLE], [TMP_BWTABLE2];
  
ELSE //In place when model is reloaded for the first time and there's no history
[BW_TABLE]:
LOAD @1 as [Material Doc_BW],
     @2 as [Mat Doc Year_BW], 
     @3 as [Posting Date_BW], 
     @4 as [Total Stock - Receipt], 
     @5 as [Total Stock - Issue], 
     @6 as [Amount in LC_BW],
     @4 + @5 as Quantity_BW
FROM
YDIORCON.CSV
(txt, codepage is 1252, no labels, delimiter is ',', msq);
 LEFT JOIN (BW_TABLE)
 LOAD 
  [Material Doc] as [Material Doc_BW],
  [Entered on]
 RESIDENT [ECC_TABLE];
 
ENDIF
STORE [BW_TABLE] INTO "History\BW_HISTORY.QVD" (qvd); //Store [BW_TABLE] for History

Hope this helps abit more

Re: How to combined two tables without using joins

Jason_Michaelides — Thu, 13 Sep 2012 07:32:45 GMT

OK - I still don't really follow what you're trying to do and I'm sure your script can be optimised better by using Exists(). Also, instead of IF,THEN ELSE, maybe load the new data from your source, then set the ErrorMode to 0, then concatenate the QVD file then set the ErrorMode back to 1.

Anyway - as for the comparisions here's a guess as I'm not sure exactly what you want. It's the same as Henric's suggestion above really.

Load whole ECC data, then flag which BW records exist in ECC:

After your ECC load and before your BW load:

Map_MaterialDoc:

MAPPING LOAD

[Material Doc]

RESIDENT ECC_TABLE;

Then, as an extra line in your BW load:

ApplyMap('Map_MaterialDoc',@1,0) AS ExistsInECC_Flag

Hope this helps,

Jason

Re: How to combined two tables without using joins

Thu, 13 Sep 2012 10:24:22 GMT

I may have found a solution.

All i need to do is basically check if a value in on table exists in another...so I did the following:


NOCONCATENATE
[Final MATDOC]:
LOAD
 [CompKey_MATDOC],
    [Entered on_MATDOC],     
    [Mat Doc_MATDOC], 
    [Posting_MATDOC]
RESIDENT [TMP_ECC_MATDOCS];
LEFT JOIN
LOAD
    [CompKey_MATDOC],
    'Y' as [Found_in_ECC]
RESIDENT [TMP_BW_MATDOCS];

I create a composite key to join on, the left join will add the field [Found in ECC] with value 'Y' if a join was possible on the compkey where there was a match.

Resulting fields in [Final MATDOC] table:

[CompKey_MATDOC] | [Entered on_MATDOC] | [Mat Doc_MATDOC] | [Posting_MATDOC] | [Found_in_ECC]

Instead of combining all the fields from one table into the other, the left join will add on the field [Found_in_ECC] if it can find an exact match in terms of keys on both tables, which is essentially what i need to do.

Re: How to combined two tables without using joins

Jason_Michaelides — Thu, 13 Sep 2012 10:37:04 GMT

Cool, although I thought you said the LEFT JOIN was slowing things down? ApplyMap() is generally faster.

Re: How to combined two tables without using joins

Thu, 13 Sep 2012 12:28:04 GMT

I did say that but for some reason if I didn't use the above approach (even though I was trying to avoid using joins) i was getting a cartesian product. I wasn't able to get the ApplyMap to work, I was getting an error (think I posted the error in a previous post).

I will keep trying to use the ApplyMap method to make the code more efficient and post the new solution, in keeping with the Topic.

Thank you all for your contribution and assistance.

Re: How to combined two tables without using joins

Fri, 21 Sep 2012 09:17:23 GMT

Hi All,

Just an update. I landed up creating a composite Key and doing a full join on the data which seemed to work. Reason for a full join was because I still needed the data that did not have a composite key that matched to illustrated what records need attention.

here is some code:

NOCONCATENATE
[TMP_ECC_MATDOCS]:
LOAD
    [Entered On]&[Material Doc] as [CompKey_MATDOC],
    [Entered On] as [Entered on_MATDOC],     
    [Material Doc] as [Mat Doc_MATDOC], 
    [Posting Date] as [Posting_MATDOC],
    [Amount in LC] as [Amount in LC_MATDOC], 
    Quantity as Quantity_MATDOC
RESIDENT [ECC_TABLE];
//WHERE [Entered on] = $(reconFileDate) - 1;
NOCONCATENATE
[TMP_BW_MATDOCS]:
 LOAD  
     [Entered on]&[Material Doc_BW] as [CompKey_MATDOC],
     [Entered on] as [Entered on_MATDOCBW],
    [Material Doc_BW] as [Material Doc_MATDOCBW],
    [Posting Date_BW] as [Posting Date_MATDOCBW],
    [Amount in LC_BW] as [Amount in LC_MATDOCBW],
    Quantity_BW as Quantity_MATDOCBW
RESIDENT [BW_TABLE];
//WHERE [Entered on] = $(reconFileDate) - 1;;
NOCONCATENATE
[Final MATDOC]: //
LOAD
    [CompKey_MATDOC],
    [Entered on_MATDOC],     
    [Mat Doc_MATDOC], 
    [Posting_MATDOC],
    [Amount in LC_MATDOC], 
    Quantity_MATDOC
RESIDENT [TMP_ECC_MATDOCS];
JOIN ([Final MATDOC])
LOAD
    [CompKey_MATDOC],
    [Amount in LC_MATDOCBW],
    Quantity_MATDOCBW,
    'Y' as [Found_in_ECC]
RESIDENT [TMP_BW_MATDOCS];

Hope it makes sense....