Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
samik_shah_lh
Contributor II
Contributor II

Conditional Load

Hi,

I am loading two set of data

Table 1: Some list of Invoice and their Purchase Order (PO) number

Table 2: A Purchase Order Record Master

Table 1 is having limited (few hundred thousand) data but Table 2 as the name suggest is has massive data (more than few millions)

So, when essentially, I am loading millions of excess record from Table 2 which is not reverent to me and just unnecessarily occupy space and make further computation complex.

So Is there any way I can do limit the load from Table 2 to the PO's which are listed in Table 1?

Any help will be appreciated as I am going mad in finding some solution in last couple of days.

Thanks,

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

Oh ok. so may be try this:

Table1:

LOAD InvoiceNum,

           @1 as [Invoice Number]

          @2 as [Purchase Order Number]

.....

FROM Tablename1;

Table2:

LOAD *

WHERE Exists([Purchase Order Number]);

LOAD PurchaseOrderRecordMaster,

          @1 as [Purchase Order Number]

          @2 as [Plant Name]

.....

FROM Tablename2;

View solution in original post

6 Replies
ramonvandelft
Contributor II
Contributor II

Hi,

If I understand you right, you should do the following:

First load Table 1

Table1:

LOAD

PO,

Field2,

Field3

FROM source

Then LOAD Table2 with a where clause:

Table2:

LOAD

PO,

Field2,

Field3

FROM source where exists (PO);

This way table 2 will only load the PO's that are previously loaded (in Table1)

vishsaggi
Champion III
Champion III

May be this? Change the field names accordingly in your 2nd table where the fieldname in your table1 should have the values in fieldname of table2. I assumed and listed the field names in WHERE Exists condition. Hope you got it.

Table1:

LOAD InvoiceNum,

           PurchaseOrderNumber

.....

FROM Tablename1;

Table2:

LOAD PurchaseOrderRecordMaster,

           .....

FROM Tablename2

WHERE Exists(PurchaseOrderNumber, PurchaseOrderRecordMaster);

samik_shah_lh
Contributor II
Contributor II

Thank you Vishwarath!

But I have used manual naming of column so I think its not working form me.

So can you please provide solution of below

Table1:

LOAD InvoiceNum,

           @1 as [Invoice Number]

          @2 as [Purchase Order Number]

.....

FROM Tablename1;

Table2:

LOAD PurchaseOrderRecordMaster,

          @1 as [Purchase Order Number]

          @2 as [Plant Name]

.....

FROM Tablename2

WHERE Exists(???, ???);

So what should I put where I have used "??" should it be the as name or the Column name (I.e. "@2")

vishsaggi
Champion III
Champion III

Oh ok. so may be try this:

Table1:

LOAD InvoiceNum,

           @1 as [Invoice Number]

          @2 as [Purchase Order Number]

.....

FROM Tablename1;

Table2:

LOAD *

WHERE Exists([Purchase Order Number]);

LOAD PurchaseOrderRecordMaster,

          @1 as [Purchase Order Number]

          @2 as [Plant Name]

.....

FROM Tablename2;

View solution in original post

qlikviewwizard
Master II
Master II

Hi,

Please check this.

Table1:

LOAD * INLINE [

InvoiceID,Purchase Order,Value1

1,PO1,1

2,PO2,2

3,PO3,3

4,PO4,4

5,PO5,5

6,PO6,6

7,PO7,7

8,PO8,8

9,PO9,9

10,PO10,10

11,PO11,11

12,PO12,12

13,PO13,13];

Table2:

LOAD * INLINE [

Purchase Order,Date,Values2

PO1,1-Jan-18,1

PO2,2-Jan-18,2

PO3,3-Jan-18,3

PO4,4-Jan-18,4

PO5,5-Jan-18,5

PO6,6-Jan-18,6

PO7,7-Jan-18,7

PO8,8-Jan-18,8

PO9,9-Jan-18,9

PO10,10-Jan-18,10

PO11,11-Jan-18,11

PO12,12-Jan-18,12

PO13,13-Jan-18,13

PO14,14-Jan-18,1

PO15,15-Jan-18,2

PO16,16-Jan-18,3

PO17,17-Jan-18,4

PO18,18-Jan-18,5

PO19,19-Jan-18,6

PO20,20-Jan-18,7

PO21,21-Jan-18,8

PO22,22-Jan-18,9

PO23,23-Jan-18,10

PO24,24-Jan-18,11

PO25,25-Jan-18,12

PO26,26-Jan-18,13

PO27,27-Jan-18,11

PO28,28-Jan-18,12

PO29,29-Jan-18,13

PO30,30-Jan-18,14]

where Exists ([Purchase Order]);

Capture.PNG

samik_shah_lh
Contributor II
Contributor II

Thank you Vishwarath!!. Its working perfectly.