Select only the order ID and add to another

[ecabanas]

Hi I have a Salesline table like this:

And i want to just only add the salesId and date to the Sales table:

any help? many many thank's

[swuehl]

It's hard to help here without knowing how your data model looks like, i.e. how the fields you are using in these tables are related, and how the expressions look like.

Could you detail the context of your two charts?

Or could you even upload a small sample QVW?

[ecabanas]

Hi,

Unfortunately I could not upload a example due the info is strictly confidential, but i'm going to explain better.

I have a table with this:

Salesline:

Sales_Id	ItemId	Date
A	1	31/01/2016
A	2	31/01/2016
A	3	31/01/2016
A	4	31/01/2016
B	1	28/02/2016

I want to create a table with this date like this:

   Salestable:

Sales_Id	Date
A	31/01/2016
B	28/02/2016

I just want to have a table with a unique SalesID and Date

Maaaaaaany thank's in advance

[swuehl]

Maybe just use Sales_Id as dimension and as expression

=Only(Date)

or create a table box with your two fields

Sales_ID and Date

[sunny_talwar]

Add Sales_Id as your dimension

and FirstSortedValue(Date, -ItemId) as your expression

or Only(Date) as your expression

[ecabanas]

Sorry guys, I miss some info, I wanna create this salestable in load script, because i'll use this table in another process

Thank's

[sunny_talwar]

May be this:

LOAD Sales_Id,

          FirstSortedValue(Date, -ItemId) as Date2,

          Only(Date) as Date1

Resident Table;

[ecabanas]

Hi Sunny,

First of all thank's again 😉

Relating the scrip, it returns to me an error:

Invalid expression

Scrap_Salestable:

LOAD

SalesBOid,

FirstSortedValue(Date_Scrap,-ItemId) as Date2,

Only(Date_Scrap) as Date1

Resident Scrap_pre

Regards

[sunny_talwar · Accepted solution]

Apologize, I forgot to add the Group By statement:

Scrap_Salestable:

LOAD

SalesBOid,

FirstSortedValue(Date_Scrap,-ItemId) as Date2,

Only(Date_Scrap) as Date1

Resident Scrap_pre

Group By SalesBOid;

[Anonymous]

Try this

Data:
Load Sales_Id, ItemId, Date#(Date,'DD/MM/YYYY') as Date Inline [
Sales_Id, ItemId, Date
A, 1, 31/01/2016
A, 2, 31/01/2016
A, 3, 31/01/2016
A, 4, 31/01/2016
B, 1, 28/02/2016
];


TableName:
LOAD Sales_Id,Date,Count(1) as CountNo
Resident Data
Group By Sales_Id,Date;

Drop Field CountNo;