topic Re: Mixing InputFiles in QlikView

Mixing InputFiles

Tue, 24 Sep 2013 08:32:08 GMT

Hi,

In the example below I try to mix different input files.
From new files only new lines may be selected. When I use “WHERE NOT EXISTS (KEY)” nothing of file
B will be selected. In my opinion the second line of file B should be selected.
That’s what I want.

//=====EXAMPLE=========================================================================
:
LOAD * INLINE [
DATE, TICKET, LINE, SHOPCODE, QTY, KEY
05-06-2013, 100, 1, SH1, 10, SH1_100_1
05-06-2013, 100, 2, SH1, 10, SH1_100_2
05-06-2013, 101, 1, SH1, 10, SH1_101_1
05-06-2013, 100, 1, SH2, 10, SH2_100_1
];

:
NOCONCATENATE LOAD * INLINE [
DATE, TICKET, LINE, SHOPCODE, QTY, KEY
05-06-2013, 100, 1, SH1, 20, SH1_100_1
05-06-2013, 102, 1, SH1, 10, SH1_102_1
];

[SALES]:
NOCONCATENATE LOAD
      DATE,
     TICKET,
     LINE,
     SHOPCODE,
     QTY,
      KEY
RESIDENT ;

CONCATENATE LOAD
      DATE,
     TICKET,
     LINE,
     SHOPCODE,
     QTY,
      KEY
RESIDENT
WHERE NOT EXISTS (KEY);

DROP TABLE ;
DROP TABLE ;

Re: Mixing InputFiles

Tue, 24 Sep 2013 09:04:14 GMT

The command "exists" determines whether a specific field value exists in a specified field of the data loaded so far, not only in the last table.

Change your script like this:

[SALES]:

NOCONCATENATE LOAD

DATE,

TICKET,

LINE,

SHOPCODE,

QTY,

KEY as KEY2

RESIDENT ;

CONCATENATE LOAD

DATE,

TICKET,

LINE,

SHOPCODE,

QTY,

KEY as KEY2

RESIDENT

WHERE NOT EXISTS ('KEY2',KEY);

Re: Mixing InputFiles

rbecher — Tue, 24 Sep 2013 09:21:05 GMT

Hi Jan,

the problem is that the keys already exists in table A/B. To solve this you have to rename the fields:

LOAD * INLINE [

DATE, TICKET, LINE, SHOPCODE, QTY, KEY_A

05-06-2013, 100, 1, SH1, 10, SH1_100_1

05-06-2013, 100, 2, SH1, 10, SH1_100_2

05-06-2013, 101, 1, SH1, 10, SH1_101_1

05-06-2013, 100, 1, SH2, 10, SH2_100_1

];

NOCONCATENATE LOAD * INLINE [

DATE, TICKET, LINE, SHOPCODE, QTY, KEY_B

05-06-2013, 100, 1, SH1, 20, SH1_100_1

05-06-2013, 102, 1, SH1, 10, SH1_102_1

];

[SALES]:

NOCONCATENATE LOAD

DATE,

TICKET,

LINE,

SHOPCODE,

QTY,

KEY_A as KEY

RESIDENT ;

CONCATENATE LOAD

DATE,

TICKET,

LINE,

SHOPCODE,

QTY,

KEY_B as KEY

RESIDENT

WHERE NOT EXISTS (KEY, KEY_B);

DROP TABLE ;

- Ralf

Re: Mixing InputFiles

Tue, 24 Sep 2013 09:40:16 GMT

Hi,

Try using DISTINCT in your script and remove NOT EXISTS while concatenating as follows:

[SALES]:

NOCONCATENATE LOAD
      DATE,
    TICKET,
    LINE,
    SHOPCODE,
    QTY,
      KEY
RESIDENT ;

CONCATENATE LOAD DISTINCT
      DATE,
    TICKET,
    LINE,
    SHOPCODE,
    QTY,
      KEY
RESIDENT

Hope this helps!

Re: Mixing InputFiles

amars — Tue, 24 Sep 2013 09:49:50 GMT

check attached file.

Thanks

Re: Mixing InputFiles

swuehl — Tue, 24 Sep 2013 09:52:09 GMT

Since you loaded all KEY values in tables A and B, the KEY exists for all values when reloading the table B in you concatenate Load.

Either Load your data directly with the last two load statements (no preloading of field values), or use exists like this:

LOAD * INLINE [

DATE, TICKET, LINE, SHOPCODE, QTY, KEY

05-06-2013, 100, 1, SH1, 10, SH1_100_1

05-06-2013, 100, 2, SH1, 10, SH1_100_2

05-06-2013, 101, 1, SH1, 10, SH1_101_1

05-06-2013, 100, 1, SH2, 10, SH2_100_1

];

NOCONCATENATE LOAD * INLINE [

DATE, TICKET, LINE, SHOPCODE, QTY, KEY

05-06-2013, 100, 1, SH1, 20, SH1_100_1

05-06-2013, 102, 1, SH1, 10, SH1_102_1

];

[SALES]:

NOCONCATENATE LOAD

DATE,

TICKET,

LINE,

SHOPCODE,

QTY,

KEY,

KEY as EXISTKEY

RESIDENT ;

CONCATENATE LOAD

DATE,

TICKET,

LINE,

SHOPCODE,

QTY,

KEY

RESIDENT

WHERE NOT EXISTS (EXISTKEY,KEY);

DROP TABLE ;

DROP FIELD EXISTKEY;

Re: Mixing InputFiles

Anonymous — Tue, 24 Sep 2013 10:28:53 GMT

Jan,

You got nothing because all values of KEY exist in B already. Your example will work if you make a small change:

[SALES]:
NOCONCATENATE LOAD
      DATE,
     TICKET,
     LINE,
     SHOPCODE,
     QTY,
      KEY,
     KEY as KEY1
RESIDENT ;

CONCATENATE LOAD
      DATE,
     TICKET,
     LINE,
     SHOPCODE,
     QTY,
      KEY
RESIDENT
WHERE NOT EXISTS (KEY1,KEY);
DROP FIELD KEY1;
...

Re: Mixing InputFiles

jagan — Tue, 24 Sep 2013 10:46:32 GMT

Hi,

Instead of reloading the tables again simply use this

LOAD

INLINE

[

NOCONCATENATE

LOAD

INLINE

[

WHERE

NOT

EXISTS

(KEY

)

;

Regards,

Jagan.

Re: Mixing InputFiles

Tue, 24 Sep 2013 10:57:04 GMT

Hey Mate,

Please see attached. I am actually not sure why your previous expression did not work and if someone could explain this behaviour with NOCONCATENATE INLINE LOADS, I would be grateful too

Cheers,

Byron

Re: Mixing InputFiles

Tue, 24 Sep 2013 11:09:49 GMT

[SALES]:

LOAD * INLINE [

DATE, TICKET, LINE, SHOPCODE, QTY, KEY

05-06-2013, 100, 1, SH1, 10, SH1_100_1

05-06-2013, 100, 2, SH1, 10, SH1_100_2

05-06-2013, 101, 1, SH1, 10, SH1_101_1

05-06-2013, 100, 1, SH2, 10, SH2_100_1

];

CONCATENATE LOAD * INLINE [

DATE, TICKET, LINE, SHOPCODE, QTY, KEY

05-06-2013, 100, 1, SH1, 20, SH1_100_1

05-06-2013, 102, 1, SH1, 10, SH1_102_1

]

WHERE NOT EXISTS (KEY);

The INLINE loads create all kind of synthetic keys.

Above code will give you the result you're looking for.

Re: Mixing InputFiles

lironbaram — Tue, 24 Sep 2013 11:13:42 GMT

your mistake is

as you have table B then all keys are exists in the model already so nothing is reloaded

you can do it like this

[SALES]:
NOCONCATENATE LOAD
      DATE,
     TICKET,
     LINE,
     SHOPCODE,
     QTY,
      KEY AS NewKEY

CONCATENATE LOAD
      DATE,
     TICKET,
     LINE,
     SHOPCODE,
     QTY,
      KEY AS NewKEY
RESIDENT
WHERE NOT EXISTS (NewKEY,KEY);

DROP TABLE ;
DROP TABLE ;

Re: Mixing InputFiles

qlikpahadi07 — Tue, 24 Sep 2013 11:25:39 GMT

your Query is not clear bit confusing. what Out put exactly want ?

Re: Mixing InputFiles

Tue, 24 Sep 2013 13:02:45 GMT

Thanks guys for your great help. Many answers where correct. It is clear to me now so I can finish my script.