topic Re: Fill missing values - problems with peek function in QlikView

Fill missing values - problems with peek function

Anonymous — Fri, 26 Aug 2016 15:10:04 GMT

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

Re: Fill missing values - problems with peek function

vishsaggi — Fri, 26 Aug 2016 19:18:59 GMT

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

Re: Fill missing values - problems with peek function

effinty2112 — Sun, 28 Aug 2016 22:09:25 GMT

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

Re: Fill missing values - problems with peek function

Anonymous — Mon, 29 Aug 2016 13:11:50 GMT

Hi Andrew,

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

Thank you!!!!

Kind regards

Lisa

Re: Fill missing values - problems with peek function

Anonymous — Mon, 29 Aug 2016 13:21:17 GMT

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