Fill missing values - problems with peek function

[Anonymous]

Hi together,

I do not understand what I make wrong. Could somebody help me please?

I want to fill missing values from table 1 to table 2.

The Table "Verlinkungung" ist the main table with keys.

Table "Ohne LS" is a temporary table to fill the missing keys.

In this example it's right that the field "LieferscheinNr" have no key.

In other cases it can be, that one Order (AuftragNr) can have one or more delivery notes (LieferscheinNr) and bills (RechnungsNr)

(1 : n Relation). I need all Bills, not just the first or last.

...

NoConcatenate

OhneLS:

LOAD AuftragNrTMP,

      LieferscheinNrTMP,

      RechnungsNrTMP

Resident Rechnungen_TMP2;

Drop Table Rechnungen_TMP2;

// -------------------------------------------------------------------- Endtabelle

// -----------------------------------------------------------------------------------

NoConcatenate

Verlinkung:

LOAD AuftragNr,

     OxidNr,

     LieferscheinNr,

     if(isNull(LieferscheinNr) and AuftragNr = peek('AuftragNrTMP', 0, 'OhneLS'), peek('LieferscheinNrTMP', 0,  'OhneLS'), LieferscheinNr) as LieferscheinNrTest,

    RechnungsNr,

     if(isNull(RechnungsNr) and AuftragNr = peek('AuftragNrTMP', 0, 'OhneLS'), peek('RechnungsNrTMP', 0, 'OhneLS'), RechnungsNr) as RechnungsNrTest

Resident Verlinkung_TMP2;

//Drop Tables Verlinkung_TMP2, OhneLS;

Drop Table Verlinkung_TMP2;

Greetings

Lisa

[vishsaggi]

Can you try this :

LOAD *,

     if(Len(LieferscheinNr) = 0 and AuftragNr = peek('AuftragNrTMP', 0, 'OhneLS'), peek('LieferscheinNrTMP', 0,  'OhneLS'), LieferscheinNr) as LieferscheinNrTest,

if(Len(RechnungsNr) = 0 and AuftragNr = peek('AuftragNrTMP', 0, 'OhneLS'), peek('RechnungsNrTMP', 0, 'OhneLS'), RechnungsNr) as RechnungsNrTest   

LOAD AuftragNr,

     OxidNr,

     LieferscheinNr,

     RechnungsNr,

Resident Verlinkung_TMP2;

If not can you post the whole temp table and the other scripts

[effinty2112 · Accepted solution]

Hi Lisa,

               I don't think you've told us enough to figure out the solution where there are multiple delivery notes and invoices for one order but for the task you're attempting to do in your script can I suggest a simpler approach?

You can

Left Join(Verlinkung)

Load * resident [Ohne LS];

Noconcatenate

Verlinkung1:

Load

AuftragNr,

     OxidNr,

     Alt(LieferscheinNr, LieferscheinNrTMP) as LieferscheinNr,

     Alt(RechnungsNr, RechnungsNrTMP) as RechnungsNr

Resident Verlinkung

Drop Tables Verlinkung, [Ohne LS];

Drop fields LieferscheinNrTMP, RechnungsNrTMP;

Rename Table Verlinkung1 to Verlinkung;

Hope this is of some help.

Kind regards

Andrew

[Anonymous]

Hi Andrew,

yes, it works perfekt. Even with several delivery notes (1 : n ).

Thank you!!!!

Kind regards

Lisa

[Anonymous]

Hi Vish,

that I have tries too, but the request for the missed fields is not the problem:

Len(LieferscheinNr) = 0

Len(RechnungsNr) = 0.

Andrews suggestion work very well. Look there.

Thank you for your help!

Kind regards

Lisa