Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Mixing InputFiles

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-20...



















;

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

DROP TABLE ;
DROP TABLE ;

1 Solution

Accepted Solutions
MVP
MVP

Re: Mixing InputFiles

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 ;

DROP TABLE ;

- Ralf

12 Replies
Not applicable

Re: Mixing InputFiles

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);

MVP
MVP

Re: Mixing InputFiles

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 ;

DROP TABLE ;

- Ralf

Not applicable

Re: Mixing InputFiles

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!

amars
Valued Contributor

Re: Mixing InputFiles

check attached file.

Thanks

MVP
MVP

Re: Mixing InputFiles

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 TABLE ;

DROP FIELD EXISTKEY;

mov
Esteemed Contributor III

Re: Mixing InputFiles

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;
...

MVP
MVP

Re: Mixing InputFiles


Hi,

Instead of reloading the tables again simply use this

 

:

 

LOAD

*

INLINE

[

 

:

 

NOCONCATENATE

LOAD

*

INLINE

[

 

WHERE

NOT

EXISTS

(KEY

)

;

Regards,

Jagan.

Not applicable

Re: Mixing InputFiles

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

Not applicable

Re: Mixing InputFiles

[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.

Community Browser