Latest date <= Today() from two SAP tables A004 and A005, Qlik Sense

[#1]

Hello Qlik Community,

I am loading two SAP tables in Qlik Sense. The name of the tables are A004 and A005. Both tables have the same columns. I search for a solution for my problem. I would like to get only one row with the latest date for each MATNR!

Table Name	KNUMH (ID)	MATNR	DATAB (Date from)
A004	2779	C1K9015	12/1/2015
A004	1920	C1K9015	1/1/2016
A004	1921	C1K9015	3/18/2016
A004	1922	C1K9000	9/1/2014

Table Name	KNUMH (ID)	MATNR	DATAB (Date from)
A005	2379	C1K9015	12/1/2015
A005	2389	C1K8001	12/12/2020
A005	1923	C1K9001	9/29/2016

Would like to get the current date, which is Current date >= DATAB (Date from):

• Latest date in the table, which is not bigger then Today(9/29/2016). (e.g. KNUMH(ID) 1921,1922 or 1923)

Tried different concepts using grouping and while loop, but couldn't get it to work. I saw a discussion, which is helpful but not the right way for my problem. Re: Load oldest date from two tables

Desired result:

Table Name	KNUMH (ID)	MATNR	DATAB (Date from)
A004	1921	C1K9015	3/18/2016
A004	1922	C1K9000	9/1/2014
A005	1923	C1K9001	9/29/2016

Thank you very much

[johanlindell · Accepted solution]

Hi,

This should do the trick:

SET DateFormat='MM/DD/YYYY';

tmp:

LOAD * Inline [

Table Name; KNUMH (ID); MATNR; DATAB (Date from)

A004; 2779; C1K9015; 12/1/2015

A004; 1920; C1K9015; 1/1/2016

A004; 1921; C1K9015; 3/18/2016

A004; 1922; C1K9000; 9/1/2014] (delimiter is ';');

Concatenate (tmp)

LOAD * Inline [

Table Name; KNUMH (ID); MATNR; DATAB (Date from)

A005; 2379; C1K9015; 12/1/2015

A005; 2389; C1K8001; 12/12/2020

A005; 1923; C1K9001; 9/29/2016] (delimiter is ';');

Table:

LOAD MATNR,

  Max ([DATAB (Date from)]) as [DATAB (Date from)]

Resident tmp

Where [DATAB (Date from)] <= Today ()

Group By MATNR;

Left Join (Table)

LOAD *

Resident tmp;

DROP Table tmp;

[#3]

johanlindell‌ thank you for your help.

[johanlindell]

HI Ibrahim,

I realized that you could get duplicates with the above script if you were to have two records of a MATNR with the same DATAB. To keep only one record you can add the following to the code.

Brgds, Johan

RENAME Table Table to Table2;

Table:

NoConcatenate

LOAD *,

  If (MATNR = Peek (MATNR)

     and [DATAB (Date from)] = Peek ([DATAB (Date from)]),

  'Delete',

  'Keep') as [Keep / Delete]

Resident Table2

Order By MATNR, [DATAB (Date from)];

DROP Table Table2;

Inner Join (Table)

LOAD 'Keep' as [Keep / Delete]

AutoGenerate (1);

DROP Field [Keep / Delete];

[#5]

Hi johanlindell‌,

thank you very much for your help. This code helps me a lot.

You are right I have two records of a MATNR with the same DATAB, but I would like to keep only the record, which contains in column Table = 'A005'

I tried a few codes, but no results. Do you know how can I handle it?

Best regards,

Ibrahim

[johanlindell]

SET DateFormat='MM/DD/YYYY';

tmp:

LOAD * Inline [

Table Name; KNUMH (ID); MATNR; DATAB (Date from)

A004; 2779; C1K9015; 12/1/2015

A004; 1920; C1K9015; 1/1/2016

A004; 1921; C1K9015; 3/18/2016

A004; 1922; C1K9000; 9/1/2014

A004; 1000; C1KXXXX; 9/1/2014] (delimiter is ';');

Concatenate (tmp)

LOAD * Inline [

Table Name; KNUMH (ID); MATNR; DATAB (Date from)

A005; 2379; C1K9015; 12/1/2015

A005; 2389; C1K8001; 12/12/2020

A005; 1923; C1K9001; 9/29/2016

A005; 1000; C1KXXXX; 9/1/2014] (delimiter is ';');

Table:

LOAD MATNR,

  Max ([DATAB (Date from)]) as [DATAB (Date from)]

Resident tmp

Where [DATAB (Date from)] <= Today ()

Group By MATNR;

Left Join (Table)

LOAD *,

  Num (Mid ([Table Name], 2, 3)) as [Table num]

Resident tmp;

DROP Table tmp;

RENAME Table Table to Table2;

Table:

NoConcatenate

LOAD *,

  If (MATNR = Peek (MATNR)

    and [DATAB (Date from)] = Peek ([DATAB (Date from)]),

  'Delete',

  'Keep') as [Keep / Delete]

Resident Table2

Order By MATNR, [DATAB (Date from)], [Table num] desc;

DROP Table Table2;

Inner Join (Table)

LOAD 'Keep' as [Keep / Delete]

AutoGenerate (1);

DROP Field [Keep / Delete];

/******************************

I just made a number out of the table name to sort the records.

If you have more tables and need to sort them you can always

make an inline table with the table names and a sort number and

join that to the tmp-table before you select the records you want. !

// Regards, Johan

******************************/

[#7]

Hello johanlindell‌

thank you very much for your help. I found another solution for my problem, but the first rows of code are your idea. I tried this code in Qlik Sense it works.

I got the following table:

Table Name	KNUMH (ID)	MATNR	DATAB (Date from)
A005	1000	C1KXXXX	9/1/2014
A004	1921	C1K9015	3/18/2016
A004	1922	C1K9000	9/1/2014
A005	1923	C1K9001	9/29/2016

Thank you again.

Best regards,

Ibrahim

SET DateFormat='MM/DD/YYYY';

A004:
LOAD * Inline [
Table Name; KNUMH (ID); MATNR; DATAB (Date from)
A004; 2779; C1K9015; 12/1/2015
A004; 1920; C1K9015; 1/1/2017
A004; 1921; C1K9015; 3/18/2016
A004; 1922; C1K9000; 9/1/2014
A004; 1000; C1KXXXX; 9/1/2014] (delimiter is ';');

A005:
Concatenate (A004)
LOAD * Inline [
Table Name; KNUMH (ID); MATNR; DATAB (Date from)
A005; 2379; C1K9015; 12/1/2015
A005; 2389; C1K8001; 12/12/2020
A005; 1923; C1K9001; 9/29/2016
A005; 1000; C1KXXXX; 9/1/2014] (delimiter is ';');

PriceTable:
LOAD MATNR,
  Max ([DATAB (Date from)]) as [DATAB (Date from)]
Resident A004
Where [DATAB (Date from)] <= Today ()
Group By MATNR;

Left Join (PriceTable)
LOAD *
Resident A004;

DROP Table A004;

[WithTableFlag]:
LOAD
  IF ([Table Name]='A004',1,0) AS [TableFlag],
  *
RESIDENT [PriceTable];

[WithoutTableFlag]:
LOAD
  *
WHERE NOT ([MATNR]=[Previous.MATNR] AND [Table Name]='A004');

LOAD
  PREVIOUS ([MATNR]) AS [Previous.MATNR],
  *
RESIDENT [WithTableFlag]
ORDER BY [MATNR],[TableFlag];

DROP TABLE [PriceTable];
DROP TABLE [WithTableFlag];

RENAME TABLE [WithoutTableFlag] TO [PriceTable];