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

Smart load. How to keep & remove some records?

sergeyay — Thu, 22 Mar 2018 09:04:09 GMT

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

];

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

olivierrobin — Thu, 22 Mar 2018 09:42:16 GMT

hello

when loading 3rd table, add a where clause

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

assuming you loaded the other tables first

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

olivierrobin — Thu, 22 Mar 2018 09:42:58 GMT

oups !!!!

it is not(exists())

sorry

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

YoussefBelloum — Thu, 22 Mar 2018 09:47:00 GMT

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

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;

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

bramkn — Thu, 22 Mar 2018 10:03:22 GMT

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

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

sergeyay — Thu, 22 Mar 2018 13:09:01 GMT

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.

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

olivierrobin — Thu, 22 Mar 2018 13:28:10 GMT

try

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

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

sergeyay — Thu, 22 Mar 2018 13:30:15 GMT

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!

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

sasiparupudi1 — Thu, 22 Mar 2018 13:35:23 GMT

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

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

;Drop Table Shipment;

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

sergeyay — Fri, 23 Mar 2018 07:39:51 GMT

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

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

YoussefBelloum — Fri, 23 Mar 2018 12:58:18 GMT

Applymap() is much faster than a join for example when you load heavy data