Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
newqlikie
Creator
Creator

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

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
newqlikie
Creator
Creator
Author

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
Master II
Master II

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

Gysbert_Wassenaar

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
Creator
Creator
Author

Hi,

with your solution I get following Error message:

Error_Message.JPG

Not expectet "TIMESTAMP".

What can I do?

newqlikie
Creator
Creator
Author

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???