topic Noob question in QlikView

Noob question

alex59800 — Mon, 27 Apr 2009 17:38:10 GMT

Hi all,

I have a problem in making a easy link between 2 files:

I made my example in this attached file. If someone could help it would be very appreciated.

Thx a lot

Alex

Noob question

Mon, 27 Apr 2009 18:03:12 GMT

Hello Alex,

I looked at your Excel sheet and suggest this solution:

STOCKS:
LOAD ARTIC1 as [Product],
 makedate(SOANNE,SOMOIS,SOJOUR) as DATE,
 SOMT1E as LASTPMP;
SQL SELECT ARTIC1,
 SOANNE,
 SOJOUR,
 SOMOIS,
 SOMT1E
FROM ... where SOJRN<>'ECA' and SOSTSA=2;

BASE:
LOAD [Product],
 max(DATE) as DATE,
LASTPMP
RESIDENT STOCKS
GROUP BY [Product];

INNER JOIN (BASE):
LOAD PRODUCT as [Product],
 QTY iN STOCK AS QTY
FROM SECOND FILE;
DROP TABLE STOCKS;

The BASE Table should contain the result you want.

Noob question

alex59800 — Mon, 27 Apr 2009 18:33:55 GMT

Hello

Thanx for yout answer but no, it's not working.

I think qlikview is loosing itselft with the "LASTPMP" in BASE:

The script is taking the good date with the max(DATE) but how can Qlikview can suceed in grouping the LASTPMP (cost price related with the last date movement).

I have an error reloading

😞

Noob question

Mon, 27 Apr 2009 18:40:33 GMT

Did you try including LASTPMP in GROUP BY:

[code[BASE:
LOAD [Product],
LASTPMP,
max(DATE) as DATE
RESIDENT STOCKS GROUP BY [Product],LASTPMP;

Noob question

alex59800 — Mon, 27 Apr 2009 19:16:31 GMT

Hello

I just tried, but it's not working either, i think the "Group by" does not work because of LASTPMP. i store BASE into a qvd to see what it looks like and he give all the movements of my stock instaed of the last date movement with the related Cost price.

In consequence, with the inner join, qlik give me the physical stock of a product X by each line he found for a same product . so my stock is totaly false. 😞

Noob question

Anonymous — Mon, 27 Apr 2009 19:38:08 GMT

How about:

1. Load the table and sort it by Product, Date descending.

2. Do a peek on Product and when it changes you add a flag with a value, 1, and let all other rows be 0. If(peek('Product')<>Product,1,0) as Flag.

3. Use the flag in the layout perhaps to only select the max(date)-row for each product, or use it on the script side to only keep those rows using a resident load and where(Flag)=1

Noob question

Mon, 27 Apr 2009 19:38:50 GMT

You are right, I decided to test it myself and the group by did not work. I fixed your problem with the following code:

Firstfile:
LOAD Product,
 DATE,
 LASTPMP
FROM FIRSTFILE;
Secondfile:
LOAD PRODUCT,
 STOCK
FROM SECONDFILE;
Result:
LOAD
Product,
max(DATE) as MDATE
RESIDENT Firstfile GROUP BY Product;
INNER JOIN (Result)
LOAD
 Product,
 DATE as MDATE,
 LASTPMP
RESIDENT Firstfile;
INNER JOIN (Result)
LOAD
 PRODUCT as Product,
 STOCK
RESIDENT Secondfile;
DROP TABLE Firstfile;
DROP TABLE Secondfile;

Noob question

alex59800 — Mon, 27 Apr 2009 22:09:54 GMT

Yes it could work. The problem i just realize, i have several similar movement with same date. The info would could distinct them is the hour.

The consequence is, qlik can not find a cost price because he's lost when he has to take it btw several movement with exact same date. So i will need to make a key to concatenate max date and make hour. An idea ?

Thx a lot

Noob question

Mon, 27 Apr 2009 22:27:26 GMT

Try Timestamp():

Firstfile:
LOAD Product,
 Timestamp(Date(DATE)&' '&Time(HOUR)) AS DATE,
 HOUR,
 LASTPMP
FROM FIRSTFILE;

Noob question

alex59800 — Sat, 02 May 2009 02:57:30 GMT

yes

After some hard work, you drove me to the right way, thank a lot.