Noob question

[alex59800]

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

[#2]

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.

[alex59800]

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

😞

[#4]

Did you try including LASTPMP in GROUP BY:

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

[alex59800]

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

[Anonymous]

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

[#7]

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;

[alex59800]

Hi

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

[#9 · Accepted solution]

Try Timestamp():

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

[alex59800]

yes

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