First date of transaction by client

[AlexWest]

Hi guys!

I have an excel table that is loaded to my Qlik Sense dashboard.

The table is showing all transactions. And I need to create a new field in Qlik Sense script and show the first transaction date of the client. And I have to realise it in the script, not in excel file.

Is it possible? If so, please help.

I searched 2 days but all I could find about this question is all advices are for SQL, but I have data only in Excel((

So:

There are 2 fields in table in Data Manager that loaded in Load Editor from Excel file:

Client | Transaction date

1 | 23.01.2022 (as you can see, this is a 1st date of transaction)

1 | 24.01.2022

1 | 04.03.2022

2 | 06.06.2022

2 | 21.08.2022

And I need to add a new field and get this result:

Client | Transaction date | First_date

1 | 23.01.2022 | 23.01.2022

1 | 24.01.2022 | 23.01.2022

1 | 04.03.2022 | 23.01.2022

2 | 06.06.2022 | 06.06.2022

2 | 21.08.2022 | 06.06.2022

(as you can see, I need just to repeat for every client his 1st date of transaction in each raw)

Thanks!

[SchalkF · Accepted solution]

Good Morning,

I would suggest loading a table that gets the first dates for each  customer, and then joining that table back with your original table.

In my example, I am not using an excel table, but an inline load, but you can replace the inline load with your excel load.

Table:
load *
inline [
Client, TransactionDate
1,23.01.2022
1,24.01.2022
1,04.03.2022
2,06.06.2022
2,21.08.2022
]
(delimiter is ',');

table_FirstDates:
Load
Client,
date(min(Date#(TransactionDate, 'DD.MM.YYYY'))) as First_Date
resident Table
group by Client;

Left Join(Table)
Load
Client,
First_Date
Resident table_FirstDates;

drop Table table_FirstDates;

Kind regards

[RafaelBarrios]

Hi @AlexWest 

i would try first a mapping table with min date for every client

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

minDateClient:

mapping load

   Client ,

   min([Transaction date]) as minDate

from <where ever you load>

group by Client;

transactions:

load

   Client ,

   [Transaction date],

  date(applyMap('minDateClient',Client)) as min_client_date

from <where ever you load>;

This can be more efficient than joining

Hope it works for you
best,

[vinieme12]

You can do the Group By and Left Join in the same pass, intermediate table is not required

temp:
load trim(Client) as Client
,date(date#(trim(Transactiondate),'dd.mm.yyyy')) as Transactiondate
inline [
Client | Transactiondate
1 | 23.01.2022
1 | 24.01.2022
1 | 04.03.2022
2 | 06.06.2022
2 | 21.08.2022
]
(delimiter is '|');

left join(temp)

Load Client,date(min(Transactiondate)) as minDate
Resident temp
group by Client;

[AlexWest]

Thanks a lot! Your way workes