Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
sergeyay
New Contributor III

Smart load. How to keep & remove some records?

Hi all!

I have some problem, but have not experience to resolve it 

There are 3 tables. 1st and 2nd is ok, but 3rd need transformations. Here is simplefied scheme:

'Shipment' has only records where %ID exists in 'Agreement'.

The rule of 'Shipment's transformation:

  • to keep records where %ShipAgrmID is empty (there is no shipment agreement);
  • to remove records where %ShipAgrmID not exists in 'ShipAgreement'.

Could you give me any idea? I think the solution is simple and beautiful ))

Regards

Sergei

[Agreement]:

LOAD * INLINE [

    %ID, #Ag_Q

    ID1, 100

    ID2, 200

    ID3, 250

];

[ShipAgreement]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Sh_Q

    ID2, A, 50

    ID2, B, 40

];

[Shipment]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Qty

    ID1, , 10

    ID2, A, 11

    ID2, A, 12

    ID3, C, 13

];

1 Solution

Accepted Solutions
olivierrobin
Valued Contributor II

Re: Smart load. How to keep & remove some records?

try

where len(trim(%ShipAgrmID))=0 or Exists(%ShipAgrmID)

10 Replies
olivierrobin
Valued Contributor II

Re: Smart load. How to keep & remove some records?

hello

when loading 3rd table, add a where clause

where isnull(%ShipAgrmID) or exists(%ShipAgrmID)

assuming you loaded the other tables first

olivierrobin
Valued Contributor II

Re: Smart load. How to keep & remove some records?

oups !!!!

it is not(exists())

sorry

YoussefBelloum
Esteemed Contributor

Re: Smart load. How to keep & remove some records?

Hi,

here is one approach (I don't have all the info like the key between the tables and table you want to keep)

[Agreement]:

LOAD * INLINE [

    %ID, #Ag_Q

    ID1, 100

    ID2, 200 

    ID3, 250

];

left keep([Agreement])  //'Shipment' has only records where %ID exists in 'Agreement'.

[Shipment_1]:

LOAD *  INLINE [

    %ID, %ShipAgrmID, #Qty

    ID1, , 10

    ID2, A, 10

    ID2, A, 10

    ID3, C, 10

];

DROP Table Agreement;

[ShipAgreement]:

LOAD AutoNumber(%ID,%ShipAgrmID) as key, *  INLINE [

    %ID, %ShipAgrmID, #Sh_Q

    ID1, A, 50

    ID2, B, 40

];

Shipment:

LOAD AutoNumber(%ID,%ShipAgrmID) as key,*

Resident Shipment_1

Where len(trim(%ShipAgrmID))=0 or Exists(%ShipAgrmID);  //this line is where I put the last two conditions you have

drop Table Shipment_1;

DROP Field %ID from Shipment;

DROP Field %ShipAgrmID From Shipment;

Partner
Partner

Re: Smart load. How to keep & remove some records?

Rename %ShipAgrmID in table two and do load of table three with a where exists on that new field name. After you can change the field name back to %ShipAgrmID

sergeyay
New Contributor III

Re: Smart load. How to keep & remove some records?

Do you meen:

[Agreement]:

... //load

[ShipAgreement]:

... //load

[Shipment]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Qty

    ID1, , 10

    ID2, A, 11

    ID2, A, 12

    ID3, C, 10   

]

where isNull (%ShipAgrmID) or Exists(%ShipAgrmID); //wrong

?

I've got 2 rows in 'Shipment', but I need 3. Sorry, maybe my picture is not correct.

Not Exists() is wrong too.

olivierrobin
Valued Contributor II

Re: Smart load. How to keep & remove some records?

try

where len(trim(%ShipAgrmID))=0 or Exists(%ShipAgrmID)

sergeyay
New Contributor III

Re: Smart load. How to keep & remove some records?

I've got it!

[Agreement]:

... //load

[ShipAgreement]:

... //load

[Shipment]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Qty

    ID1, , 10

    ID2, A, 11

    ID2, A, 12

    ID3, C, 10  

]

where %ShipAgrmID = '' or Exists(%ShipAgrmID); //not Null but empty string !

Now it's ok, thank you!

sasiparupudi1
Honored Contributor III

Re: Smart load. How to keep & remove some records?

Try using applymaps

LOAD * INLINE [

    %ID, #Ag_Q

    ID1, 100

    ID2, 200

    ID3, 250

];

MAP_Agreement:

Mapping Load

%ID, #Ag_Q

Resident

Agreement;

Drop table Agreement;

[ShipAgreement]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Sh_Q

    ID2, A, 50

    ID2, B, 40

];

MAP_ShipAgreement:

Mapping Load

%ID&'|'&%ShipAgrmID,#Sh_Q

Resident

ShipAgreement;

Drop Table ShipAgreement;

[Shipment]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Qty

    ID1, , 10

    ID2, A, 11

    ID2, A, 12

    ID3, C, 13

];

Final:

Noconcatenate Load

  %ID, %ShipAgrmID, #Qty

 

  Resident

Shipment

WHERE

Len(Trim(%ShipAgrmID))=0

Or

Isnull(ApplyMap('MAP_ShipAgreement',%ID&'|'&%ShipAgrmID,Null()))=0

;Drop Table Shipment;

sergeyay
New Contributor III

Re: Smart load. How to keep & remove some records?

What advantage to use applymaps? The code became much complicated. But I like the result!