Load only data that exists in spreadsheets

[C1109]

Hi all, 

I have one large data table with unique reference numbers for all events but I only want to load the data for those reference numbers that exist in three separate spreadsheets (keeping the data from each spreadsheet separate). 

e.g. 

Data Table	Spreadsheet A	Spreadsheet B	Spreadsheet C
1			1
2	2		2
3		3
4	4		4
5
6			6
7		7
8			8

So I only want the following loaded:
Spreadsheet A = reference numbers 2 and 4 (loaded as Ref A)
Spreadsheet B = reference numbers 3 and 7 (loaded as Ref B)
Spreadsheet C = reference numbers 1, 2, 4, 6 and 8 (loaded as Ref C)

Hoping this makes sense to someone! 

[lfetensini]

SpreadsheetA:
LOAD
 Data,
 FIELD as RefA
FROM [lib://FOLDER/File.xls](biff, embedded labels, table is SpreadsheetA)
WHERE Len(Trim(RefA))> 0;

SpreadsheetB:
LOAD
 Data,
 FIELD as RefB
FROM [lib://FOLDER/File.xls](biff, embedded labels, table is SpreadsheetB)
WHERE Len(Trim(RefB))> 0;

SpreadsheetC:
LOAD
 Data,
 FIELD as RefC
FROM [lib://FOLDER/File.xls](biff, embedded labels, table is SpreadsheetC)
WHERE Len(Trim(RefC))> 0;

Now, if you just need keep tables separated, insert NOCONCATENATE, example:

NOCONCATENATE
SpreadsheetC:
LOAD
 Data,

[rwunderlich]

Load the spreadsheets first and then load the big table with a Where Exists() clause:

BigTable:
LOAD RefNo, other fields, etc
FROM BigTable.qvd (qvd) // or wherever
Where Exists([Ref A], RefNo)
  or Exists([Ref B], RefNo)
  or Exists([Ref C], RefNo)
;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[C1109]

Hi Rwunderlich, 

Your suggestion loads just the spreadsheet records I want... thank you 😀

However it doesn't let me bring in the rest of the data from the Big Table associated with those records which is what I need to do 😕

[C1109]

Thanks lftensini, 

Loading the spreadsheets separately isn't the problem I'm afraid... I need to be able to load data from my Big Table that relates to only to those entries in each of the spreadsheets. 

[rwunderlich]

I don't understand. My example did not show loading any spreadsheet records, only the big table data. 

What do you mean by "it doesn't let me bring in the rest of the data from the Big Table associated with those records"?  

-Rob

[C1109]

Hi Rob, 

Thanks for your patience... it's difficult to explain as I don't know the terminology and I cannot share the actual data due to its nature so everything is "examples". 

Firstly there is a BigTable of data that lists every event of every type with a reference number (field name EVENTNO).

Then I have 3 spreadsheets (A, B and C) that each contain different pieces of data to the BigTable except they have the same reference numbers as those in the BigTable with field names EVENTNO_A, EVENTNO_B and EVENTNO_C.

I have been asked to create one app that displays the data for each spreadsheet separately (they do not link in any way) but I need to add in the data from the BigTable for the records in each spreadsheet. 

Does that make more sense now? 

[rwunderlich · Accepted solution]

It makes perfect sense. And I believe it's exactly what I showed in my example, so perhaps we are misunderstanding each other. I used "RefNo" based on your first post instead of EVENTNO but the idea is the same. 

1. Load the spreadsheets.  I didn't show this,  assume you got this part. After loading, the fields EVENTNO_A, EVENTNO_B and EVENTNO_C will exist in your data model. 

2.  I don't know what the source of your BigTable is, but I'll assume it's a QVD.

BigTable:
LOAD EVENTNO, other fields, etc
FROM BigTable.qvd (qvd) // or wherever
Where Exists(EVENTNO_A, EVENTNO)
  or Exists(EVENTNO_B,EVENTNO)
  or Exists(EVENTNO_C,EVENTNO)
;

This load will load rows from BigTable.qvd where EVENTNO matches a value previously loaded in field EVENTNO_A, or EVENTNO_B or EVENTNO_C?  Is that what you are looking for?

Keeping things separate for analysis is a separate issue, let's see if we can clarify this first. 

-Rob 

[C1109]

Good morning Rob, 

Thank you again for your assistance and huge apologies! 

Your suggestion was exactly correct (the missing data was my mistake!!). 

All working perfectly now 😁