Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

newqlikie
Contributor

Problem with CONCATENATE, STORE and WHERE function of 2 tables.

Hi everybody,

I have following problem.

I have one Datasource with always adding data. If we send a new parcel, the table will be added:

See example:

Packaging-Table.JPG

The Problem ist, that the oldest entries (Date, Time) in that table will be deleted after a while. But I will keep them in QlikView.

So, I will try following:

Send1:

LOAD    DATE,

                TIME,

                WEIGHT,

                TIMESTAMP

FROM

C:\colli.qvd

(qvd);

Send2:

CONCATENATE

                LOAD DATE&TIME as TIMESTAMP

Select       DATE,

                TIME,

                WEIGHT

FROM QS36F.PACKAGING

                WHERE SEND2.TIMESTAMP > SEND1.TIMESTAMP;

STORE * FROM Send2 INTO  C:\colli.qvd

But I do not know how to do it right and I can not find anything in the web about it.

I need to compare the timestamp from table 2 (Send2) with the timestamp in Table 1 (Send1) to get only the data from table "Send2" which is not contained in table "Send1".

Can soembody help me?

I appreciate your fast help.

regards ...

Tags (1)
6 Replies

Re: Problem with CONCATENATE, STORE and WHERE function of 2 tables.

Hi,

     You can search for Incremental Scenario's, If you want to store that data into QlikView.

     Here is the Link

     http://community.qlik.com/docs/DOC-1870

Regards,

Kaushik Solanki

newqlikie
Contributor

Re: Problem with CONCATENATE, STORE and WHERE function of 2 tables.

Hi,

thank you for your answer,

But I can not transfer these examples into my script.

Can you help me by using my script for the solution?

Thanks again.

sushil353
Honored Contributor II

Re: Problem with CONCATENATE, STORE and WHERE function of 2 tables.

Hi Try This:

Send1:
LOAD    DATE,
        TIME,
        WEIGHT,
        TIMESTAMP
FROM
C:\colli.qvd
(qvd);

CONCATENATE
          LOAD

                                  DATE,

                TIME,

                WEIGHT,

DATE&TIME as TIMESTAMP
Select       DATE,
                TIME,
                WEIGHT
FROM QS36F.PACKAGING
                WHERE NOT Exists(TIMESTAMP);
STORE * FROM Send1 INTO  C:\colli.qvd

HTH

Sushil

Re: Problem with CONCATENATE, STORE and WHERE function of 2 tables.

Maybe something like this:

Send1:

LOAD   DATE,  TIME,  WEIGHT,  TIMESTAMP

FROM C:\colli.qvd (qvd);

Temp;

LOAD max(TIMESTAMP) as MaxDT resident Send1;

LET vMaxDT = peek('MaxDT');

DROP TABLE Temp;

SELECT  DATE,  TIME,  WEIGHT, DATE+TIME TIMESTAMP

FROM QS36F.PACKAGING

WHERE (DATE+TIME) > $(vMaxDT);

STORE Send1 INTO C:\colli.qvd;

Note: You'll have to create a proper timestamp to be able to compare with. Date+Time or Date&Time is probably not good enough.


talk is cheap, supply exceeds demand
newqlikie
Contributor

Re: Problem with CONCATENATE, STORE and WHERE function of 2 tables.

Hi,

with your solution I get following Error message:

Error_Message.JPG

Not expectet "TIMESTAMP".

What can I do?

newqlikie
Contributor

Re: Problem with CONCATENATE, STORE and WHERE function of 2 tables.

Hi Gisbert,

I have the problem by combining Date and Time together in the WHERE command.

I tried following, but it is not working:

FROM QS36F.VERPAC

    WHERE  (dat003 CONCAT uhr003) > $(vMaxDT) and VERAKZ=3;

    STORE * from Versendet1 into \\dufile1\dentaldaten\QlikView\QVD\LieferantenQVD\Packstuecke.qvd;

   

Do you have any idea???

Community Browser