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

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

newqlikie — Fri, 05 Apr 2013 08:15:01 GMT

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:

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

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

kaushiknsolanki — Fri, 05 Apr 2013 08:26:21 GMT

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

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

newqlikie — Fri, 05 Apr 2013 08:37:38 GMT

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.

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

sushil353 — Fri, 05 Apr 2013 08:48:34 GMT

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.

Gysbert_Wassenaar — Fri, 05 Apr 2013 09:09:09 GMT

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.

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

newqlikie — Fri, 05 Apr 2013 09:13:12 GMT

Hi,

with your solution I get following Error message:

Not expectet "TIMESTAMP".

What can I do?

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

newqlikie — Fri, 05 Apr 2013 10:55:15 GMT

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