Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
AlexWest
Creator
Creator

First date of transaction by client

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!

Labels (4)
1 Solution

Accepted Solutions
SchalkF
Contributor III
Contributor III

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

View solution in original post

4 Replies
SchalkF
Contributor III
Contributor III

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
Partner - Specialist
Partner - Specialist

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
Champion III
Champion III

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;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
AlexWest
Creator
Creator
Author

Thanks a lot! Your way workes