Dynamic Input Cross Tab creating a unique identifier

[Kutay]

Hello Everyone,

I have a dataset with this format 

Record	JSON Name	JSON Value String
1	0.adtagID	6565
2	0.distinctpageseen	1
3	0.firsttimeseen	2020-02-05
4	0.lasttimeseen	2020-02-05
5	1.adtagID	6587
6	1.distinctpageseen	1
7	1.firsttimeseen	2020-02-05
8	1.lasttimeseen	2020-02-05
9	1.profiletime	1

and convert it into sth like this by using text to columns function for "json name" and then use cross tab .

Record	AdTagID	Distinctpageseen	firsttimeseen	lasttimeseen	profiletime
1	6565	1	2020-05-05	2020-05-05	N/A
2	6587	1	2020-05-05	2020-05-05	1

The problem is that I get same document every week with same "json name" column. If I use a directory and dynamic input to automate the process I get duplicated values as i have for week1.report  for example "0.adtagID" with a value and for week.2.report  "0.adtagID" with another value. How can I create a unique identifier for each documents coming from dynamic input automatically (such as file name ) so that I can solve this problem ?

Problem looks like this:

Week 1

Record	JSON Name	JSON Value String
1	0.adtagID	6565
2	0.distinctpageseen	1
3	0.firsttimeseen	2020-02-05
4	0.lasttimeseen	2020-02-05
5	1.adtagID	6587
6	1.distinctpageseen	1
7	1.firsttimeseen	2020-02-05
8	1.lasttimeseen	2020-02-05
9	1.profiletime	1

Week 2:

Record	JSON Name	JSON Value String
1	0.adtagID	6565
2	0.distinctpageseen	3
3	0.firsttimeseen	2020-02-12
4	0.lasttimeseen	2020-02-12
5	1.adtagID	6587
6	1.distinctpageseen	5
7	1.firsttimeseen	2020-02-13
8	1.lasttimeseen	2020-02-13
9	1.profiletime	6

Crosstab:

Record	AdTagID	Distinctpageseen	firsttimeseen	lasttimeseen	profiletime
1	6565,6565	1,3	2020-05-05,2020-05-12	2020-05-05,2020-05-12	N/A,N/A
2	6587,6565	1,5	2020-05-05,2020-05-13	2020-05-05,2020-05-13	1,6

Solution that I am looking for :

Record	AdTagID	Distinctpageseen	firsttimeseen	lasttimeseen	profiletime
1	6565	1	2020-05-05	2020-05-05	N/A
2	6587	1	2020-05-05	2020-05-05	1
3	6565	3	2020-05-12	2020-05-12	N/A
4	6565	5	2020-05-13	2020-05-13	6

Thank you all for your help ! 

[grossal]

Hi @Kutay,

you can pass a field through the Dynamic Input, e.g. the file name.

This file name + the Record ID could be used as an unique ID and you could use a normal RecordID Tool in the end to get "real" Record numbers instead of your unique IDs.

Best

Alex

[AbhilashR · Accepted solution]

Hi @Kutay, you could modify the Output File Name as Field option in the Dynamic Input tool to File Name Only to retrieve the file names of incoming files.

EDIT - I have included a sample workflow for you to look through. Below is an image of the workflow setup I have to bring in the FileName.

[Kutay]

Hi Alex thank you for your help. I tried your solution but got that  error "at least one modification must be specified"

[grossal]

Ohh sorry, I forgot that you have to use the 'Replace a String' option to avoid this.

Just write some random String and choose a field of your choice (ideally not the filename, but something else). This will allow you to pass a field.

[Kutay]

It worked perfectly. I just added the File Name also in the cross tab function to group by and got the desired output. Thank you so much  !!