Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

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 NameKNUMH (ID)MATNRDATAB (Date from)
A0042779C1K901512/1/2015
A0041920C1K90151/1/2016
A0041921C1K90153/18/2016
A0041922C1K90009/1/2014

Table NameKNUMH (ID)MATNRDATAB (Date from)
A0052379C1K901512/1/2015
A0052389C1K800112/12/2020
A0051923C1K90019/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 NameKNUMH (ID)MATNRDATAB (Date from)
A0041921C1K90153/18/2016
A0041922C1K90009/1/2014
A0051923C1K90019/29/2016

Thank you very much

1 Solution

Accepted Solutions
Partner
Partner

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

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;

6 Replies
Partner
Partner

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

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;

Not applicable

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

johanlindell‌ thank you for your help.

Partner
Partner

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

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];

Not applicable

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

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

Partner
Partner

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

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

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

Not applicable

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

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 NameKNUMH (ID)MATNRDATAB (Date from)
A0051000C1KXXXX9/1/2014
A0041921C1K90153/18/2016
A0041922C1K90009/1/2014
A0051923C1K90019/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];