row number for id and date and then if condition

[#1]

hello all,

i have some problem for example:

there is customer id, date, document number and Invoice

Customer id	Date	Document Number	Invoice Ammount	Flag
20001	04.11.2016	51000	30	x
20001	10.10.2016	51001	40	x
20001	09.09.2016	51045	80	x
20001	14.11.2016	49900	20	y
20022	13.11.2916	51006	30	x
20022	02.10.2016	51002	81	x
20022	09.11.2016	49903	44	x
20122	04.11.2016	49777	55	x
20122	10.10.2016	51050	66	x
20122	09.09.2016	52011	43	x
20122	16.11.2016	52044	22	y
20122	13.11.2916	51007	90	y

i want to create flag on the basis of customer id and date. if one customer have 4 dates or document number than last max date will be called Y as flag. like in id 2001 for 14.11.2016

like in id 20022 we have only three date than the flag set to X for all three document number.

and in id 20122 we have five dates than for max 2 dates the flag set to Y.

customer id and date field are not present in same table. not even these two table are directly linked to each other. there is one table in between.

so first they should be in one table.

thanks in advance.

Regards,

Sneh

Message was edited by: sneh sharma

[Anil_Babu_Samineni]

I am not fully understand this? Do you mean this, If so, OK or else you may explain bit more

[rubenmarin]

Hi sneh, you can try with this script:

DataOrig:

LOAD [Customer id], Date(Date#(Date, 'DD.MM.YYYY')) as Date, [Document Number], [Invoice Ammount] Inline [

Customer id, Date, Document Number, Invoice Ammount

20001, 04.11.2016, 51000, 30

20001, 10.10.2016, 51001, 40

20001, 09.09.2016, 51045, 80

20001, 14.11.2016, 49900, 20

20022, 13.11.2916, 51006, 30

20022, 02.10.2016, 51002, 81

20022, 09.11.2016, 49903, 44

20122, 04.11.2016, 49777, 55

20122, 10.10.2016, 51050, 66

20122, 09.09.2016, 52011, 43

20122, 16.11.2016, 52044, 22

20122, 13.11.2916, 51007, 90

];

Keys:

LOAD [Customer id],

  Min(Date, 4) as MinDate,

  Count(Date) as CountDate

Resident DataOrig Group by [Customer id];

Left Join (DataOrig) LOAD * Resident Keys Where CountDate>=4;

Data:

NoConcatenate

LOAD [Customer id],

  Date,

  [Document Number],

  [Invoice Ammount],

  If(Date>=MinDate, 'y', 'x') as Flag

Resident DataOrig;

DROP table DataOrig;

[antoniotiman]

Hi,

another way

Temp:
LOAD [Customer id],
Date(Date#(Date,'DD.MM.YYYY')) as Date,
[Document Number],
[Invoice Ammount]
FROM
"https://community.qlik.com/message/1161780"
(html, codepage is 1252, embedded labels, table is @1);

Table:
Load *,AutoNumber(RowNo(),[Customer id]) as Count
Resident Temp
Order By [Customer id],Date;
Drop Table Temp;
Load *,If(Count >= 4,'y','x') as Flag
Resident Table;
Drop Table Table;
Drop Field Count;

Regards,

Antonio

[#5]

Hello Ruben,

Thank you so much

i have one question or i forget to say that my date field and customer id are not coming from same table. Document number and customer id tables are directly linked.

so Customer id field from table1

Document number field from table2

date field from table3

Table1 and table2 are directly linked to each other.

i have big data model with more than 35 tables.

Regards,

Sneh

[#6]

Hello Antonio,

Thank you so much

i have one question or i forget to say that my date field and customer id are not coming from same table. Document number and customer id tables are directly linked.

so Customer id field from table1

Document number field from table2

date field from table3

Table1 and table2 are directly linked to each other.

i have big data model with more than 35 tables.

Regards,

Sneh

[rubenmarin]

Hi sneh, if you want to create a flag and you need all 3 fields to generate the flag maybe will be needed to join the tables (at least in a temporal table) to create the flag...

And to wich table should this flag belong? Is the document number an unique key? If it is, you can do a left join by document number and flag with this table to set the flag to each of the document number.

If this is not the case please upload a sample to work.