Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sergeyay
Contributor III
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
Specialist III
Specialist III

try

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

View solution in original post

10 Replies
olivierrobin
Specialist III
Specialist III

hello

when loading 3rd table, add a where clause

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

assuming you loaded the other tables first

olivierrobin
Specialist III
Specialist III

oups !!!!

it is not(exists())

sorry

YoussefBelloum
Champion
Champion

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;

bramkn
Partner - Specialist
Partner - Specialist

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
Contributor III
Contributor III
Author

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
Specialist III
Specialist III

try

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

sergeyay
Contributor III
Contributor III
Author

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
Master III
Master III

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
Contributor III
Contributor III
Author

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