topic Re: Conditional Load in QlikView

Conditional Load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Conditional Load

ramonvandelft — Tue, 28 Aug 2018 15:25:42 GMT

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)

Re: Conditional Load

vishsaggi — Tue, 28 Aug 2018 15:27:55 GMT

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);

Re: Conditional Load

Anonymous — Tue, 28 Aug 2018 18:56:11 GMT

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")

Re: Conditional Load

vishsaggi — Tue, 28 Aug 2018 19:19:54 GMT

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;

Re: Conditional Load

qlikviewwizard — Wed, 29 Aug 2018 03:33:42 GMT

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]);

Re: Conditional Load

Anonymous — Wed, 29 Aug 2018 14:51:34 GMT

Thank you Vishwarath!!. Its working perfectly.