Load Data with the help of a List

[Loeckli]

Hello Everybody

I am trying to load a table with the data that does not appear in the list.

// In this section I load all data that have the status R. This is because the projects can have several statuses, ultimately I only want to load the projects that have only one entry with A.

DB:
NoConcatenate
Load
Distinct
Projectnr ,
Name,
Status,
Resident DB_2 Where Match(Status, 'R')and Wildmatch(Name,'*US*');

// Then saving all the Projectnr. which have an entry as a list

let vProjectnr = Projectnr;

// Final DB where projects only have entrys as status A

Final_DB:
NoConcatenate
Load
Projectnr ,
Resident DB_2 Where not WildMatch(Projectnr ,'vProjectname');

My code does not work, if there are better approaches I'm open to hear about them.

Thanks in advance

[RafaelBarrios]

HI @Loeckli

i guess that you are trying to get Projectnr from the first table by
let vProjectnr = Projectnr;

but this will not work

you will need to use a while or For statement in order to read the first table row by row, and then use it in the next load.

you can try this

Let vNoOfRows = NoOfRows('DB');
for i=0 to vNoOfRows;
Let vProjectnr = Peek('Projectnr', $(i),DB);

Final_DB:
//NoConcatenate You need to concatenate, to avoid concatenating with previous one, create a dummy field
Load
1 as dummy_field,
*
Resident DB_2 Where not WildMatch(Projectnr ,'$(vProjectname)');
next i;

another approach would be to use Join and keep so the iteration is not needed.

hope this helps.

best.

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 😁

[Loeckli]

Thank you very much for your reply.

My code now :

DB:
NoConcatenate
Load
Distinct
Projectnr ,
Name,
Status,
Resident DB_2 Where Match(Status, 'R')and Wildmatch(Name,'*US*')

Let vNoOfRows = NoOfRows('DB');
for i=0 to vNoOfRows;
Let vProjectnr = Peek('Projectnr', $(i),DB);

Final_DB:
Load
1 as dummy_field,
*
RResident DB_2 Where not WildMatch(Projectnr ,'$(vProjectnr )') and WildMatch(Status, '*A*');
next i;
Drop Table Freigabe_DB;

However this code gives me not the desired output, in the final_db are still ProjectNr with the status R.

[barnabyd]

G'day @Loeckli,

I think that 'where not exists( ... )' might be what you're looking for:

DB:
NoConcatenate
Load Distinct
    Projectnr,
    Projectnr as Projectnr_R,
    Name,
    Status
Resident DB_2 Where Match(Status, 'R')and Wildmatch(Name,'*US*');

// Final DB where projects only have entrys as status A
Final_DB:
NoConcatenate
Load
    Projectnr
Resident DB_2 Where not exists( Projectnr_R, Projectnr );

You need to give Projectnr a different name where status is 'R', otherwise the not exists will exclude everything in the DB_2 table as well.

Cheers, Barnaby.