Dividi in colonna, un po' complicato L’argomento è risolto

[AndreaB.]

Ciao a tutti,
ho provato a cercare in rete ma con risultati pessimi, questo molto probabilmente dovuti all'errato utilizzo delle parole chiave.

Cerco di spiegare il più chiaramente possibile il mio problema.

Da un foglio CSV importo in excel dei dati relativi a prezzi di titoli e devo verificare che il prezzo al quale abbiamo eseguito una determinata operazione sia stato il migliore; queste informazioni vengono raggruppate in un unico campo.

Il problema mi si pone quando il titolo in questione è un titoli Americano in quanto il prezzo viene espresso in 32esimi e non in decimale (il problema del prezzo l'ho risolto), in quanto il prezzo può essere separato da uno spazio.

Faccio un esempio pratico:
Titolo non Americano: ALLQ:AKRO@106.95 JPAT@106.973000 GSEB@NA
Titolo Americano: ALLQ:MSTY@82-08 1/4 HSI@82-08 GSTY@82-07 7/8 RBS@82-06 3/4 DB@NA


Ora nel primo caso se uso come separatore nell'ordine:
1. : (i due punti);
2. @;
3. lo spazio.

Ottengo il risultato desiderato:
ALLQ | AKRO | 106.95 | JPAT | 106.973000 | GSEB | NA

Però non riesco a trovare un metodo per ottenere per i titoli americani la seguente divisione:
ALLQ | MSTY | 82-08 1/4 | HSI@82-08 | GSTY |82-07 7/8 | RBS | 82-06 3/4 | DB | NA


vi ringrazio per ogni dritta che potrete darmi.

saluti

Andrea

[AndreaB.]

Ho assoluto bisogno del vostro aiuto per creare il Linguaggio M corretto (sempre che si possa fare)

Ho pensato ad una soluzione del genere ma non so proprio come scriverla.

1. conto lunghezza del record
2. Ciclo sul contenuto di ogni carattere del record
con Text.PositionOf([Audit Trail]," ") trovo la posizione in cui si trova il primo spazio, verificando se il carattere successivo isnumber = falso allora sostituisco <spazio> con @
3: una volta finito faccio un testo in colonna di cui al post precedente con solo i due punti e la @


potrebbe andare?

se sì come si fa?

[AndreaB.]

Ho fatto un piccolo passo in avanti.
So per certo che il numero dopo lo spazio può essere solo >0

quindi ho, temporaneamente, aggiunto una colonna, per cercare di creare un codice M accettabile, nella quale identifico se il carattere successivo AL PRIMO spazio sia una numero o no, se sì riporto il numero, altrimenti metto uno 0

Codice: Seleziona tutto

Table.AddColumn( #"Modificato tipo con impostazioni locali1", "Personalizzato.2", each try Number.From( Text.At([Audit Trail], Text.PositionOf([Audit Trail]," ")+1)) otherwise 0)

ora sono nuovamente bloccato, perché non ho la benché minima idea di come verificare se ci sia un ulteriore spazio ....

continuo a studiare e vi tedierò nuovamente .

Grazie a chi nel frattempo mi vorrà aiutare

[Andrea90]

AndreaB.,

Se può aiutarti, al fine di essere "sicuro" di non avere più di uno spazio vuoto tra stringhe puoi trasformare la stringa di testo con questa formula che ti allego. Al momento l'ho impostata come Table.AddColumn, pertanto partendo da una colonna che contiene la stringa di testo da analizzare, ti andrà a rimuovere tutti gli spazi in eccesso (ovviamente se esiste un solo spazio tra due parole quello verrà mantenuto).

Codice: Seleziona tutto

Text.Combine( List.Select(Text.Split([Stringhe], " "), (x)=> x <> ""), " ")

Il campo [Stringhe] è quello che contiene la stringa da analizzare (tu cambierai il riferimento al campo reale del tuo file).

Andrea

[AndreaB.]

Ciao Andrea,
ti ringrazio per il suggerimento, ma non mi sono spiegato correttamente io.

La stringa può avere più spazi vuoti ma non contigui, e dopo enne posizioni (dove enne non è costante).

Grazie ancora

Andrea

[Enrico Galli]

Ciao, puoi inviare un estratto del csv anche di poche (ma significative) righe? Potrebbe essere utile una pre-elaborazione, magari in altro ambiente, prima di importarla in Power Query

[AndreaB.]

Allego un estratto con le due casistiche che mi si presentano

questa la stringa nel cvs:
ALLQ:AKRO@106.95 JPAT@106.973000 GSEB

questo il risultato desiderato (e questo risultato riesco ad ottenerlo):
ALLQ AKRO 106,95 JPAT 106,973 GSEB

questa la stringa nel csv:
ALLQ:MSTY@82-08 1/4 HSI@82-08 GSTY@82-07 7/8 RBS@82-06 3/4 DB@NA

questo il risultato desiderato (questo risultato non capisco proprio come ottenerlo):
ALLQ MSTY 82-08 1/4 HSI 82-08 GSTY 82-07 7/8 RBS 82-06 3/4 DB NA



e questo è il codice che ho scritto fino ad ora:

Codice: Seleziona tutto

let
    Origine = Csv.Document(File.Contents(pathServer & Best),[Delimiter=",", Columns=32, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}}),
    #"Rimosse prime righe" = Table.Skip(#"Modificato tipo",2),
    #"Intestazioni alzate di livello" = Table.PromoteHeaders(#"Rimosse prime righe", [PromoteAllScalars=true]),
    #"Filtrate righe" = Table.SelectRows(#"Intestazioni alzate di livello", each ([Alloc Status] <> "")),
    #"Rimosse colonne" = Table.RemoveColumns(#"Filtrate righe",{"Block Status", "Rcvd Time", "SetDt", "Seq#", "App", "Workflow", "ReferenceID", "Customer", "Net", "Dest", "Price (Fra)", "OrigQty", "ASW Spread", "Dealer MiFID Firm", "Security (ASX)", "DlrsCmp", "Brkr", "OTC PT Ind"}),
    #"Modificato tipo con impostazioni locali" = Table.TransformColumnTypes(#"Rimosse colonne", {{"Trade Dt", type date}}, "en-US"),
    #"Modificato tipo con impostazioni locali1" = Table.TransformColumnTypes(#"Modificato tipo con impostazioni locali", {{"Quantity", type number}}, "en-US"),
    #"Aggiunta colonna personalizzata2" = Table.AddColumn( #"Modificato tipo con impostazioni locali1", "Personalizzato.2", each try Number.From( Text.At([Audit Trail], Text.PositionOf([Audit Trail]," ")+1)) otherwise 0)
in
    #"Aggiunta colonna personalizzata2"

Spero di aver reso l'idea.

Grazie per l'interessamento.

[Enrico Galli]

Ho "partorito" questo script: prova!

Codice: Seleziona tutto

let
    Origine = Csv.Document(File.Contents(pathServer & Best),[Delimiter=",", Columns=32, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Rimosse prime righe" = Table.Skip(Origine,2),
    #"Intestazioni alzate di livello" = Table.PromoteHeaders(#"Rimosse prime righe", [PromoteAllScalars=true]),
    #"Filtrate righe" = Table.SelectRows(#"Intestazioni alzate di livello", each ([Alloc Status] <> "")),
    #"Rimosse colonne" = Table.RemoveColumns(#"Filtrate righe",{"Block Status", "Rcvd Time", "SetDt", "Seq#", "App", "Workflow", "ReferenceID", "Customer", "Net", "Dest", "Price (Fra)", "OrigQty", "ASW Spread", "Dealer MiFID Firm", "Security (ASX)", "DlrsCmp", "Brkr", "OTC PT Ind"}),
    #"Modificato tipo con impostazioni locali" = Table.TransformColumnTypes(#"Rimosse colonne", {{"Trade Dt", type date}}, "en-US"),
    #"Modificato tipo con impostazioni locali1" = Table.TransformColumnTypes(#"Modificato tipo con impostazioni locali", {{"Quantity", type number}}, "en-US"),
    #"Aggiunta colonna personalizzata2" = Table.AddColumn( #"Modificato tipo con impostazioni locali1", "newcode", (x) => Text.Combine(List.Transform(Text.Split(x[#"Audit Trail"], " "), each if List.Contains({"1".."9"}, Text.Start(_, 1)) then "|" & _ else _), "|")),
    #"Sostituito valore" = Table.ReplaceValue(#"Aggiunta colonna personalizzata2",":","|",Replacer.ReplaceText,{"newcode"}),
    #"Sostituito valore1" = Table.ReplaceValue(#"Sostituito valore"," ","|",Replacer.ReplaceText,{"newcode"}),
    #"Sostituito valore2" = Table.ReplaceValue(#"Sostituito valore1","@","|",Replacer.ReplaceText,{"newcode"}),
    #"Sostituito valore3" = Table.ReplaceValue(#"Sostituito valore2","||"," ",Replacer.ReplaceText,{"newcode"}),
    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Sostituito valore3", "newcode", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"newcode.1", "newcode.2", "newcode.3", "newcode.4", "newcode.5", "newcode.6", "newcode.7", "newcode.8", "newcode.9", "newcode.10", "newcode.11"})
in
    #"Suddividi colonna in base al delimitatore"

[Andrea90]

AndreaB.,

Io invece ho partorito, se ti può servire, questa funzione personalizzata:

La funzione custom l’ho chiamata MyFunct (devi chiamarla allo stesso modo)

Codice: Seleziona tutto

(ori_string as text, start_string as text, myindex as number) as text=>
let
    Result =
            if
                    Text.At(ori_string, myindex) <> " "
            then
                    start_string & Text.At(ori_string, myindex)
            else
                    if try Value.Is(Number.From(Text.At(ori_string, myindex + 1)), Number.Type) otherwise Value.Is("a", Number.Type)
                    then
                         start_string & Text.At(ori_string, myindex)
                    else
                         start_string & "@"                   
in
    Result

e poi ho creato una colonna nella tabella di partenza che quando trova uno spazio seguito da una lettera mette un “@“, non so se era quello che volevi.

La formula da inserire nella colonna calcolata è:

Codice: Seleziona tutto

List.Last(List.Generate(
()=>[Start=0, Funct=MyFunct([Stringhe], "", 0), String=[Stringhe]],
each (try [Funct])[HasError]=false,
each [Start=[Start]+1,Funct= MyFunct([String], [Funct], Start), String=[String]]
))[Funct]

Andrea

[AndreaB.]

Grazie infinite ad entrambi.

Al momento ho utilizzato la soluzione di Enrico ...
Per quella di Andrea temo che ho ancora parecchio da capire, studiare, imparare e soprattutto ASSIMILARE per poter solo immaginare di farla mia.

Ma come si dice l'appetito vien mangiando ...

poi prometto che chiudo il topic, mi avete già fatto un immenso regalo.

Pensavo di convertire in numero decimale il valore che riporta nel campo [newcode.3] se il campo [ISIN] non inizia con "US" e ho provato con questa soluzione (che non funziona)

Codice: Seleziona tutto

= Table.AddColumn(#"Suddividi colonna in base al delimitatore", "newcode.3.1", each if Text.Start([ISIN],2)<>"US" then each try Number.From(#"Suddividi colonna in base al delimitatore", {{[newcode.3], type number}}, "en-US") else [newcode.3])

dove si verifica la condizione mi da il risultato ma mi scrive Function.

inoltre avrei la necessità di inserire nell'else questa funzione excel che mi sono creato per restituire il prezzo espresso in 32esimi in decimale:

Codice: Seleziona tutto

Public Function Prezzo32esimi(price As Variant) As Double

Dim arg1 As Integer ' verifico presenza carattere - nel prezzo (indice di prezzo espresso in 32esimi)
Dim arg2 As Integer ' verifico presenza carattere / nel prezzo
Dim arg3 As Integer ' verifico presenza carattere + nel prezzo
Dim arg4 As Integer ' verifico presenza carattere <spazio> nel prezzo


  arg1 = InStr(price, "-")
  arg2 = InStr(price, "/")
  arg3 = InStr(price, "+")
  arg4 = InStr(price, " ")

  If arg1 > 0 Then
    ' è presente quindi il prezzo è espresso in 32esimi
    Prezzo32esimi = Left(price, arg1 - 1)
    If arg2 > 0 Then
      Prezzo32esimi = Prezzo32esimi + (Mid(price, arg1 + 1, (arg4 - 1) - (arg1 - 1))) / 32 + (Mid(price, arg2 - 1, 1) / Mid(price, arg2 + 1, 1) / 32)
    ElseIf arg3 > 0 Then
      Prezzo32esimi = Prezzo32esimi + (Mid(price, arg1 + 1, (arg3 - 1))) / 32 + (1 / 64)
    End If
  Else
    ' non è presente quindi riporto il valore già espresso
    Prezzo32esimi = price
  End If
End Function

Ma non ho la benché minima idea di come fare

Grazie a tutti per le spiegazioni che vorrete fornirmi.